Copying Dynamic Ranges

[oggieben1956]

Hi,
I have several sheets of data (same format etc, just the sizes vary) that I need to combine into one sheet which acts as the base data for a pivot table. The date changes regularly. I tried using the recorder but this just puts in actual cells. I have looked at VBA, but I am still unable to fathom out what exactly I need to do.
Essentially, the requirement is to first of all remove the date first of all in the destination sheet, then for each of the data sheets, select all of the data and then copy that into the destination sheet at the next available row.
Thanks in advance for any advice on how to do this.
Alternatively, if I define a name for a sheet as $a and then only copy rows/cols in that range that has data in it????

[royUK]

Have you got header rows on the data sheet?

[ravishankar]

Hi
Insert a sheet called summary. paste the following codes in the macro window ( Alt F11)

Please Login or Register  to view this content.

Run the macro. It lists sheet names in col A and data from col B
Ravi

[oggieben1956]

Thanks to you both for replying.
I do have headers in all of the source sheets and the target sheets.
So I set a name for A1 in each sheet with the intention of the macro going to A1 in the target, going down 1 row and then clearing all of the date from that point downwards. Then go to A1 of the first source sheet, move 1 down and then select all of the data downwards, copy and then go back to the target A1, down to the next available A cell and then paste. The slect and copy/paste would then be repeated for each source.
The macro I recorded is as follows:
Sub Load_DB()
Application.Goto Reference:="DB_Base" ' top left of target
Range(Selection, Selection.End(xlDown)).Select
Range("A2:F22").Select
Application.CutCopyMode = False
Selection.ClearContents
Application.Goto Reference:="Dates_Base" ' top left of first source
Range(Selection, Selection.End(xlDown)).Select
Range("A2:F66").Select
Selection.Copy
Application.Goto Reference:="DB_Base"
ActiveSheet.Paste
Application.Goto Reference:="Cup_Base" ' top left of second source
Range(Selection, Selection.End(xlDown)).Select
Range("A1:F79").Select
Application.CutCopyMode = False
Selection.Copy
Application.Goto Reference:="DB_Base"
Selection.End(xlDown).Select
Range("A67").Select
ActiveSheet.Paste
Application.Goto Reference:="NG_Base"
Range(Selection, Selection.End(xlDown)).Select
Range("A1:F70").Select
Application.CutCopyMode = False
Selection.Copy
Application.Goto Reference:="DB_Base"
Selection.End(xlDown).Select
Range("A146").Select
ActiveSheet.Paste
Application.Goto Reference:="Post_Base"
Range(Selection, Selection.End(xlDown)).Select
Range("A1:F204").Select
Application.CutCopyMode = False
Selection.Copy
Application.Goto Reference:="DB_Base"
Selection.End(xlDown).Select
Range("A216").Select
ActiveSheet.Paste
Application.Goto Reference:="Home_Base"
Range(Selection, Selection.End(xlDown)).Select
Range("A2:E351").Select
Application.CutCopyMode = False
Selection.Copy
Application.Goto Reference:="DB_Base"
Selection.End(xlDown).Select
Range("B420").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.Goto Reference:="Away_Base"
Range(Selection, Selection.End(xlDown)).Select
Range("A2:E351").Select
Application.CutCopyMode = False
Selection.Copy
Application.Goto Reference:="DB_Base"
Selection.End(xlDown).Select
Range("B419").Select
Selection.End(xlDown).Select
Range("B770").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.Goto Reference:="Clue"
Application.CutCopyMode = False
Selection.Copy
Application.Goto Reference:="DB_Base"
Selection.End(xlDown).Select
Range("B419").Select
Selection.End(xlDown).Select
Range("A1115:A1119").Select
Range("A1119").Activate
Selection.End(xlUp).Select
Range("B419").Select
Selection.End(xlDown).Select
Range("A1119").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Range("A1118").Select
Range(Selection, Selection.End(xlUp)).Select
Range("A420:A1118").Select
Range("A1118").Activate
ActiveSheet.Paste
End Sub


The problem is that the Macro has specific cells in which defeats the object of selecting and copying dynamic ranges.

Thanks

Chris

I did try the macro from Ravi bit I ended up with a 1004 run-time error (PasteSpecial method of Range class failed.

[ravishankar]

Hi
try the macro inside this workbook. It works at my end.
Ravi