Moving blocks of data across worksheets

[craiglittleperth]

Hi All,

Can you help? I have a excel workbook with 100tabs, I need to collect
data from all to put into a single sheet, the data is in a floating
range, it always starts at the same point, is the same width, but can
be hundereds of lines.......

All sets have a different name, in a specific cell... i need to copy
this and replicate down for each line????

I hope that makes enough sense....

Thanks in advance

Craig

 

[Guest]

assuming the data is isolated in that there are no filled cells adjacent to
it that you do not want to copy.

Assume it always starts in b9 and there are no blank rows or columns within
the data range

Sub copyData()
Dim sh as worksheet, sh1 as Worksheet
dim rng as Range, r1 as Range
set sh = worksheets("Summary") '<== change as needed
for each sh1 in worksheets
if sh1.Name <> sh.Name then
set rng = sh1.Range("B9").Currentregion
set r1 = sh.cells(rows.count,1).End(xlup)(2)
rng.Copy r1
end if
next
End sub