Double Entries

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, intLimit, intX, 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

Hi Spike, welcome to the forum! :)

As a preliminary, I'd just like to point out that this
Dim currentMonth, intLimit, intX, intCurrentRow As Integer

is not understood as
Dim currentMonth As Integer, intLimit As Integer, intX As Integer, intCurrentRow As Integer

by VB, as it is natural to assume, but instead as
Dim currentMonth As Variant, intLimit As Variant, intX As Variant, intCurrentRow As Integer

So, you may want to amend your declaration if you want all of them to be ints, as the names suggest.

Now, to your question. Depending on how the new rows are entered, it may be simple or not so simple to accomplish what you're after. Specifically, if the new data are always entered underneath the old data, it's a simple matter of replacing the constant lower bound of your loop (2) by the last row of the old data. If the new data are inserted between the old, but in a contiguous block, it's quite similar.
If, however, the new data are inserted in various places, one would need a wholly different and more complex strategy - something like having the user mark the new rows somehow, or making a temporary copy of the sheet and then comparing row by row.

Hi there Cas

Thanks for the reply. The code that i am using was written by someone else and i am just adjusting it to do what i need it to do. I will change the code to what you advised.

About the entries. I am just adding the info straight after the last entry. I don't do VB at all so any help would be great.

Also after sorting the sorting stops at 205 and won't go beyond that.

Thanks

Hi again, sorry for the delay. This is really quite a bit more complicated than I initially thought, even in the simple scenario. I put together some example code that stores and retrieves the previously used last cell in a Name (Names are one of the convenient ways of making Excel save and load information along with the workbook) and based on that copying the new values in column A on sheet 1 to column A on sheet 2, replacing the old values on sheet 2 (that last part is contrary to your requirements, but seemed better for demonstration purposes).

You might want to copy this code into a fresh workbook and play around with it a little before trying to adapt it to your needs. You will need to look up the individual statements in the VB Help.
Public Sub getNewItems()

'find the first cell to copy
Dim firstCell As Range
'by retrieving the previously used last cell
Dim thisName As Name, prevCellName As Name
For Each thisName In ThisWorkbook.Names
If thisName.Name = "prevCell" Then Set prevCellName = thisName
Next
If prevCellName Is Nothing Then 'not found -> create the name and use first cell in sheet
Set prevCellName = ThisWorkbook.Names.Add("prevCell", vbNullString)
Set firstCell = Sheet1.Range("A1")
Else 'found -> use cell directly below previous cell
Set firstCell = prevCellName.RefersToRange.Offset(1, 0)
End If

'the last cell is the last one containing data in the same block as firstCell
Dim lastCell As Range: Set lastCell = firstCell.End(xlDown)
'the source range is what lies between firstCell and lastCell on Sheet1
Dim sourceRange As Range: Set sourceRange = Sheet1.Range(firstCell, lastCell)
'the target range is an equally sized range on Sheet2
Dim targetRange As Range: Set targetRange = Sheet2.Range("A1").Resize(sourceRange.Rows.Count)

'execute the transfer
targetRange.Value = sourceRange.Value

'finally store lastCell as the new prevCell
prevCellName.RefersTo = "=" & lastCell.Address

End Sub

If anyone can think of a much simpler way of doing this sort of thing, please intervene and stop me... :-\