VBA to copy row from sheets to another based on cell value

[chivis2504]

Hello everyone,

I'm really new in VBA. I tried to figure out this by myself using other codes I found online, but I have errors.

I have 4 sheets (New York, California, Virginia, and Summary). The first 3 have values in columns A to O.

I need a code to copy the complete row from the 3 sheets (New York, California, Virginia) and paste it in the next available row in sheet "Summary" based on the value in the column "STATUS" (Column N). If value is equal to one of the following "Submit CLNT, Intvw CLNT, Submit CUST, Intvw CUST" then copy to next available row in sheet "Summary".

Also, if possible, in the sheet “Summary” I need rows to be delete it if the value in the column "STATUS" in the 3 first sheets changes to any other than "Submit CLNT, Intvw CLNT, Submit CUST, Intvw CUST"

I really appreciate all the help. Below is the code that I have been working. Thank you!


Sub Copy_Rows_If()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim Lastrowa As Long
Sheets("New York”, “California”, “Virginia ").Activate
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Lastrowa = 3 'Sheets("Summary").Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To Lastrow
If Sheets("New York”, “California”, “Virginia ").).Cells(Rows.Count, "status").Value = "Submit clnt" Then
Sheets("New York”, “California”, “Virginia ").).Rows(i).Copy
Sheets("Summary ").Rows(Lastrowa).PasteSpecial xlPasteValues
Lastrowa = Lastrowa + 1
End If
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

 

[onlyadrafter]

Hello,

here is the code for the first part, getting the rows to the summary sheet.

Sub SUMMARY()
    For MY_SHEETS = 1 To 3
    With Sheets(MY_SHEETS)
        For MY_ROWS = 1 To .Range("A" & Rows.Count).End(xlUp).Row
            If .Range("N" & MY_ROWS).Value = "Submit CLNT" Or .Range("N" & MY_ROWS).Value = "Intvw CLNT" _
                Or .Range("N" & MY_ROWS).Value = "Submit CUST" Or .Range("N" & MY_ROWS).Value = "Intvw CUST" Then
                .Rows(MY_ROWS).Copy Sheets("Summary").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
            End If
        Next MY_ROWS
    End With
    Next MY_SHEETS
End Sub

does this work as expected?

The only way I can see of deleting rows in the summary sheet, is to check for changes in the status column, then clear out the summary sheet, and re-run the code above.