View Full Version : Double Entries

06-11-2008, 07:18 AM

I am having a problem with one of my macro's. I have a excel main sheet and all the months on a different sheets in the same document. On the main sheet i have a end date and the macro takes the end date and copies that line to the correct month. Now if i go into the document tomorrow and add loads more line in the master documents and then press go it re-sorts and copies all the line even the old ones into the months making double entries. Is there a way to for it to check or stop making the double entries and only add the new lines.

Here is the code i have for sorting it:

Option Explicit
Const CountColumn = 15 ' the column in which the count formula is to be entered

Private Sub Go_Click()

Dim currentMonth As Variant, intLimit As Variant, intX As Variant, intCurrentRow As Integer
Dim months
months = Array("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")
ThisWorkbook.Sheets("January").Cells(1, CountColumn) = "=count(A:A)+1"
ThisWorkbook.Sheets("February").Cells(1, CountColumn) = "=count(A:A)+1"
ThisWorkbook.Sheets("March").Cells(1, CountColumn) = "=count(A:A)+1"
ThisWorkbook.Sheets("April").Cells(1, CountColumn) = "=count(A:A)+1"
ThisWorkbook.Sheets("May").Cells(1, CountColumn) = "=count(A:A)+1"
ThisWorkbook.Sheets("June").Cells(1, CountColumn) = "=count(A:A)+1"
ThisWorkbook.Sheets("July").Cells(1, CountColumn) = "=count(A:A)+1"
ThisWorkbook.Sheets("August").Cells(1, CountColumn) = "=count(A:A)+1"
ThisWorkbook.Sheets("September").Cells(1, CountColumn) = "=count(A:A)+1"
ThisWorkbook.Sheets("October").Cells(1, CountColumn) = "=count(A:A)+1"
ThisWorkbook.Sheets("November").Cells(1, CountColumn) = "=count(A:A)+1"
ThisWorkbook.Sheets("December").Cells(1, CountColumn) = "=count(A:A)+1"
ThisWorkbook.Sheets("Master").Cells(1, CountColumn) = "=count(A:A)+1"

Dim tmpSheetName As String
intLimit = Int(ThisWorkbook.Sheets("Master").Cells(1, CountColumn).Value)

For intX = 2 To intLimit
currentMonth = Int(ThisWorkbook.Sheets("Master").Cells(intX, 14).Value) - 1
tmpSheetName = months(currentMonth)
ActiveSheet.Range("A" & intX & ":M" & intX).Select
intCurrentRow = Int(ThisWorkbook.Sheets(tmpSheetName).Cells(1, CountColumn).Value) + 1
ActiveSheet.Range("A" & intCurrentRow).Select
Selection.PasteSpecial (xlPasteAll)
Next intX

End Sub

Help would be appreciated.

06-11-2008, 09:36 AM

Question. Does any one day's import contain mixed transaction dates or do all those transactions have the same transaction date? Why do you sort the table before copying to the month worksheets?

Suggestion: add a column to your table for a boolean "Posted? (Y/N=blank)." You can sort on this and the date (in that order) to isolate the lines needing to be copied and allowing you to copy all of those for each month as a block for a little efficiency. After copying and pasting, update the Posted flag and re-sort the transactions by date.

Can you post a sample file with any incriminating ('er proprietary) information obfuscated? That would help us help you.


06-11-2008, 12:54 PM
Can you post a workbook containing sample date?. Use Manage Attachments in the Go Advanced section.

Andy Pope
06-12-2008, 04:49 AM
Cross post

06-12-2008, 11:52 AM
Please read (http://www.excelguru.ca/node/7)