Macro to copy specific rows between worksheets

[andrija_bgd]

Hi,

I am looking to build a macro that would copy data from 8 worksheets into the 9th worksheet. Each of the 8 worksheets contains 4 tables I need to copy. They are situated one under another, separated by a few rows. table names are stored one cell above the tables themselves. The tables don't have the same number of columns. Is there a way for me to do this?

Cheers,

Andrija

 

[RSXchin]

are the worksheets blank other than the titles? are any of the other cells merged? other than the titles I mean.

and do you want to have empty rows inbetween on the 9th sheet?

 

[andrija_bgd]

The rows should not be empty between the pasted data on the 9th sheet. It is supposed to be an aggregation of the information on the previous 8 sheets. Here is what I have so far (I'm getting an error actually, am troubleshooting as we speak):

Sub TestAS()

Sheets("B-S").Select
If Range("A1").End(xlDown).Value = "" Then
Sheets("B01-BUY-Pre-eff date preDec11").Select
Range(Cells.Find(What:="5a) Buy / Sell Template", LookIn:=xlValues, LookAt:=xlWhole), Cells.Find(What:="5a) Buy / Sell Template", LookIn:=xlValues, LookAt:=xlWhole).End(xlDown)).EntireRow.Offset(1, 0).Copy
Sheets("B-S").Select
Range("A1").End(xlDown).PasteSpecial xlPasteAll
Application.CutCopyMode = False
Else
Sheets("B01-BUY-Pre-eff date preDec11").Select
Range(Cells.Find(What:="5a) Buy / Sell Template", LookIn:=xlValues, LookAt:=xlWhole), Cells.Find(What:="5a) Buy / Sell Template", LookIn:=xlValues, LookAt:=xlWhole).End(xlDown)).EntireRow.Offset(2, 0).Copy
Sheets("B-S").Select
Range("A1").End(xlDown).PasteSpecial xlPasteAll
Application.CutCopyMode = False
End If

End Sub

I'm currently working only on a solution for one of the tables - I figure if I get this to work I can replicate the logic for the others. The idea is to copy the first table in its entirety (with the headers) if there is no data in 9th sheet. If there is, it would copy just the data below the headers (hence offset 2 instead of 1). The error I'm getting is on the first run of "Range("A1").End(xlDown).PasteSpecial xlPasteAll"

also, no cells are merged

Cheers,

Andrija