MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Collapsing Data (URGENT)

Posted by amit on January 03, 2002 10:17 AM

I have to collapse about 50 sheets (workbooks) of data into a single sheet (workbook),There is a header row with 16 variables in each sheet and I just want all the data concatenated into one sheet ,HELP

Posted by Jacob on January 03, 2002 11:44 AM


Assuming you want all the pages copies to a new sheet each one starting on a row after the one above it finished. i.e. sheet 1 has data from a1:g100 then that would be copies to a new sheet and the next sheet would start at g101 to whatever.

Sub TransferMyJunk()
sheets.add = "Consolidated Data"
for x = 1 to sheets.count

If not sheets(x).name = "Consolidated Date" then
sheets(x).range("A1:G" & range("A65536").end(xlup).row).copy
range("A" & range("A65536").end(xlup).offset(1,0).select
end if
next x

application.cutcopymode = false
End Sub

Modify as needed.