Spike_UK
06-11-2008, 07:18 AM
Afternoon
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)
Sheets("Master").Select
ActiveSheet.Range("A" & intX & ":M" & intX).Select
Selection.Copy
Sheets(tmpSheetName).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.
Thanks
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)
Sheets("Master").Select
ActiveSheet.Range("A" & intX & ":M" & intX).Select
Selection.Copy
Sheets(tmpSheetName).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.
Thanks