Macro to copy row based on condition

Rachel541 Posts 1 Registration date Tuesday December 21, 2021 Status Member Last seen December 21, 2021 - Dec 21, 2021 at 09:22 AM
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 - Dec 23, 2021 at 12:00 PM
Hello,

I need to set-up a macro to copy rows from Sheet 1 to Sheet 6 based on data in column 'AS' (If data ="Yes" the row is copied over)

I need it to past from row A4 downwards on sheet 6 due to headers.

Is it possible to only copy over rows with new data (not duplicate data in sheet 6 each time the macro is run and also not delete the ones already copied before). ie- add the new row of data into sheet 6 when yes is entered into the column, but not disturb the rows already copied over previously as more data may be added to these rows after they have been copied over in sheet 6?

I've tried to create a macro but it either duplicates all the rows each time I run the macro, or it only pastes into row A4 on sheet 6 and doesn't copy over all the rows need.

In case its needed for the macro code: the macro needs to copy data from row 3 to row 5000.

Thanks!

1 reply

TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Dec 23, 2021 at 12:00 PM
Hi Rachel,

In the code below, Sheet 1 is called "Sheet1" and sheet 6 "Sheet6".
What would you consider a duplicate? In the code below, the value from column A needs to be unqiue for the row to be copied over.

Here is the code:
Sub RunMe()
Dim mFind As Range

Sheets("Sheet1").Select

For Each cell In Range("A3:A5000")
    If cell.Offset(0, 44).Value = "Yes" Then
        Set mFind = Sheets("Sheet6").Columns("A").Find(cell.Value)
        If mFind Is Nothing Then cell.EntireRow.Copy Sheets("Sheet6").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    End If
Next cell

End Sub


You will have to wait till next year for further assistance from me.

Best wishes and a happy new year!
Trowa
0