Copy Paste Data from one Excel Worksheet to Another Using VBA

How to copy and paste data from one Excel Worksheet to Another Using VBA

I want to search the whole spreadsheet for the product ‘Car’ and paste the whole row (not just the cell). So in this instance it would be row a2, a5, a8 and a10 from sheet 1 to sheet2.
If I have to use macros to automate the whole process what would be the vba code?

Sub mycar()
‘Let’s start at row 2. Row 1 has headers
x = 2
‘Start the loop
Do While Cells(x, 1) <> “”
‘Look for data with ‘Car’
If Cells(x, 1) = “Car” Then
‘copy the row if it contains ‘Car’
Worksheets(“Sheet1”).Rows(x).Copy
‘Go to sheet2. Activate it. We want the data here
Worksheets(“Sheet2”).Activate
‘Find the first empty row in sheet2
erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
‘Paste the data here
ActiveSheet.Paste Destination:=Worksheets(“Sheet2”).Rows(erow)
End If
‘go to sheet1 again and actvate it
Worksheets(“Sheet1”).Activate
‘Loop through the other rows with data
x = x + 1
Loop
End Sub

Watch the Microsoft Excel training video below to see how you can use the macro (vba code) to copy and paste specific data from Sheet 1 to Sheet 2: