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

chivis2504

New Member
Joined
Aug 27, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

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

VBA Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,420
Messages
5,624,695
Members
416,042
Latest member
Oden

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top