Macro to copy row based on conditionClosed

Question

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!

TrowaD · Answer

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

Macro to copy row based on condition

1 response

Similar discussions

Subscribe To Our Newsletter!