Copy Paste Data from one Excel Worksheet to Another

Excel 2007 Excel 2003 Macros Excel 2007 Macros Excel 2003 Excel 2010
Home



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:




Home
Excel 2007 Excel 2003 Macros Excel 2007 Macros Excel 2003 Excel 2010