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 