Duplication data every time macro is run, instead of updating newly found data only

Hi,

I had posted my problem before and thought it was solved after receiving great help.

(http://www.excelforum.com/excel-prog...ml#post2266996)

However, I just found out that there is a slight problem with the code I had received (for my purpose). It keeps duplicating the data every time the macro is run, while I need it to just update the new data, found in the worksheet I work on daily, in the specified new worksheet.

Any help on solving this would be very much appreciated. Cheers.

Here's the code and I have also included the sample excel file:

Sub UpdateInvoiceSheet()
    Dim ws1 As Worksheet: Dim ws2 As Worksheet
    Dim rRpt As Range
    
    Set ws1 = Worksheets("Receipts PYMT 2010")
    Set ws2 = Worksheets("Invoice Cost Overview 2010")
    
    y = ws2.UsedRange.Rows.Count + 1
    For Each rRpt In ws1.Range("E2:E" & ws1.UsedRange.Rows.Count)
        If rRpt = 0.7 Or rRpt = 1 Then
            ws2.Cells(y, "B") = ws1.Cells(rRpt.Row, "I")
            ws2.Cells(y, "C") = ws1.Cells(rRpt.Row, "A")
            ws2.Cells(y, "D") = ws1.Cells(rRpt.Row, "G")
            y = y + 1
        End If
    Next rRpt
End Sub

Hi Lostinxcel

Try this

Sub UpdateInvoiceSheet()
    Dim ws1 As Worksheet: Dim ws2 As Worksheet
    Dim rRpt As Range

    Set ws1 = Worksheets("Receipts PYMT 2010")
    Set ws2 = Worksheets("Invoice Cost Overview 2010")

    y = ws2.UsedRange.Rows.Count + 1
    For Each rRpt In ws1.Range("E2:E" & ws1.UsedRange.Rows.Count)
        If rRpt = 0.7 Or rRpt = 1 Then
            If Not rRpt.Offset(0, 8).Value = "x" Then 'put this marker column anywhere you want
                ws2.Cells(y, "B") = ws1.Cells(rRpt.Row, "I")
                ws2.Cells(y, "C") = ws1.Cells(rRpt.Row, "A")
                ws2.Cells(y, "D") = ws1.Cells(rRpt.Row, "G")
                y = y + 1
                rRpt.Offset(0, 8).Value = "x" 'after you've used the item, mark it so it's not duplicated
            End If
        End If
    Next rRpt
End Sub

The code places a marker in Receipts PYMT 2010 when an Item is used so it doesn't get used again.

Hope this helps.

John