Macro to copy row based on condition

Report
Posts
1
Registration date
Tuesday December 21, 2021
Status
Member
Last seen
December 21, 2021
-
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
-
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

Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
491
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