how to copy only new row(s) from one sheet to bottom of others based on criteria

[Rita Bush]

Good Morning,

I've combed through your Forum most of yesterday and can't figure out how to do this - sorry. The attached spreadsheet is a simple sample of how the actual workbook is set up. There will be an endless amount of rows as time goes on. I found some code which I have no idea how to write, but I tried my hand at adding some data to match this sheet. What we are trying to do is that each time new information is added, we would like it to copy that entire row and add it to the bottom of the sheet that matches the criteria. In this case, it is the Store name. When I use the code I found, it does copy initially. Then when I add a new row and run the Macro, it adds all the rows instead of just the new ones. I highlighted what is happening on the Shop sheet tab. Is there a way for the code to be modified to add only all new rows to the individual sheet(s) for each Store? I hope this makes sense. This is only my 2nd post in 6 years, and you all were so helpful. Thanks for any advice on this project.

[davesexcel]

Hello,
What determines a new item?

[Rita Bush]

Good Afternoon,
I'm sorry to just get back to this question. Our project person was out at a conference, and has returned. They did not think about how to determine a new item. If we incorporated a field to include a checkbox, would that work in a formula if it was only checked when new, then once copied to another sheet they could uncheck manually?

[vcoolio]

Hello Rita,

Dave might have something else in mind but here's a starting point:-

Sub Test()

    Dim ar As Variant, i As Integer
    Dim ws As Worksheet: Set ws = Sheets("MasterSheet")
    ar = [{"Gardens","Dogs","Shop","Veggie";"Gardens-r-us","Dogs Eat Here","Shop Here","Veggie Shop"}]

Application.ScreenUpdating = False

    For i = 1 To UBound(ar, 2)
        Sheets(ar(1, i)).UsedRange.Offset(1).Clear
        With ws.[A1].CurrentRegion
                    .AutoFilter 4, ar(2, i)
                    .Offset(1).EntireRow.Copy Sheets(ar(1, i)).Range("A" & Rows.Count).End(3)(2)
                    Sheets(ar(1, i)).Columns.AutoFit
                    .AutoFilter
        End With
 Next i
    
Application.ScreenUpdating = True

End Sub

I've attached your sample workbook with the code implemented and assigned to Scooby's image.. Click on Scooby's image to run the code.

BTW, you added your code to the ThisWorkbook module. I have removed it in the attached sample as it is not an event code and needed to be placed in a standard module. Check the Project Explorer to see what I mean.

I hope that this helps.

Cheerio,
vcoolio.