copy/paste macro...

**Nickster64** · 02-18-2009, 04:57 PM

I have a total of 30 sheets in my workbook. I would like to copy rows 21:1000 from sheets 5 - 27 and paste them into sheet 4 starting on row 21.

The data ranges vary within sheets 5 - 27. For example, sheet 6 has data in rows 21:50, whereas sheet 10 has data in rows 21:500. I chose 21:1000 as a safe measure to ensure that all data starting on rows 21 onward gets copied.

I am having trouble thinking of how to create a macro which will copy the data from sheets 5 - 27 and paste into sheet 4 leaving no blank spaces in between pasted rows. Ultimately my goal is to use the filter boxes I have set up in the column headers of sheet 4 to quickly sort all the data pasted from sheets 5 - 27.

Thanks for your input!

Nick

**StephenR** · 02-18-2009, 05:27 PM

Can you explain more about your data or attach an example sheet. Could the data extend across any number of columns? Are there any completely blank rows in your data. If not, then a simple loop through your sheets could add the data to the bottom of sheet4.

**davegugg** · 02-18-2009, 05:46 PM

Try:

Private Sub PastetoSheetFour()

Dim i as integer

For i = 5 to 27
     Sheets(i).Activate
     Range("A65536").End(xlup).Select
     Range(Activecell,"A21").Select
     Selection.Copy
     Sheets(4).Activate
     Range("A65536").End(xlup).Offset(1, 0)Select
     ActiveSheet.Paste

Next i

End Sub

Dave

**Nickster64** · 02-19-2009, 11:17 AM

Thanks for your responses. Davegugg, I tweaked with your macro and it works great now. Below is what I the final version of it.

Dim i As Integer                 

For i = 5 To 27                 
     Sheets(i).Activate          
     Range("A21:K1000").Select  
     Selection.Copy              
     Sheets("EngDepts").Select 
     Range("A60000:K60000").End(xlUp).Offset(1, 0).Select  
     ActiveSheet.Paste           

Next i

Thank you for your help!

Nick