Macro to Copy/Paste Data into one wksht, skipp one row before past

[Shoney]

Hi,

I'm working w/about 100 tabs that contain data. I have a macro that will
copy and paste the data and have tested it on three tabs (recorded macro). I
also have a macro to delete empty rows between data. I could continue to
record the macro for all of the tabs, but as you can imagine, that may take a
while.

The range of the copy/paste data is exactly the same for each sheet. Some
sheets will have up to 52 rows populated across colums M:AN, but some will
only have 4 rows populated, as all null values are returned as blank
(intentionally). All of the sheets have actual names (not "Sheet 1", etc.).
I would like to have all of the data pasted to one sheet, named "Copy.Paste".

Is there any way to have the macro run for each sheet, pasting data
underneath the previous entry, leaving only one completely empty row between
each set of data?

One other thing, the amount of sheets may increase or decrease over time, so
I would need the macro to run for all of the sheets up until the "Copy.Paste"
sheet.

Is this possible?

I appreciate any help you can provide.

Thanks,

Shoney

 

[Dave Peterson]

I'm confused, but maybe...

Dim wks as worksheet
dim RngToCopy as range
dim DestCell as range

'first paste goes here.
Set DestCell = worksheets("copy.paste").range("a1")

for each wks in activeworkbook.worksheets
if lcase(wks.name) = lcase("copy.paste") then
'skip it
else
set rngtocopy = wks.range("M1:AN52")
rngtocopy.copy _
destination:=destcell
set destcell = destcell.offset(rngtocopy.rows.count,0)
end if
next wks

========
Untested, uncompiled. Watch for typos.