# Append large number of tables on Excel sheets to one master table



## Dunk (Sep 14, 2010)

Hi there,

I have obtained an Excel 2010 workbook containing 1339 sheets of tables. The tables are all in the exact same format and represent an itinerary. Each sheet was extracted from a different pdf page and each entry is unique. There are 34 row entries on each sheet and each entry has 9 columns.

I need to be able to combine all 1339 tables into one master table on one sheet so I can sort and filter as one.

Many posts have discussed the Consolidate function however this involves naming ranges for each sheet. Obviously I can't do this easily for 1339 sheets.

Is there a way I can get all the data appended or concatenated into one master sheet? I have tried importing to Access 2010 to help but it only appears to allow one sheet at a time.

I feel like this should be easy so apologize if it is in fact.

Thanks


----------



## bomb #21 (Jul 1, 2005)

> Many posts have discussed the Consolidate function however this involves naming ranges for each sheet. Obviously I can't do this easily for 1339 sheets.


Maybe "obviously", maybe not.

If you have a three-sheet book & define a name (example) "test" as:

*=!$A$1:$I$34*

then omitting the sheet *name* from the definition makes it "global" & thus applicable to _whichever_ sheet is "active" at the time.

Who knows if it would work for *1339* sheets though? 



> There are 34 row entries on each sheet and each entry has 9 columns.


Do they have "headers"?


----------



## Dunk (Sep 14, 2010)

Found this post - a variant on the macro gave the desired result:

http://excel.tips.net/T003005_Condensing_Multiple_Worksheets_Into_One.html

Very pleased with it!


----------



## bomb #21 (Jul 1, 2005)

ASAP has "Sheets » Put together rows or columns from several sheets..." and loads more besides. Consider acquiring it.


----------



## etaf (Oct 2, 2003)

I had a similar issue and one of the members here wrote this piece of code for a problem I had - only issue is it needs to be modified to include all the Rows - Mine exclude the 1st 26 row and used row 26 as a header, I'm sure a VBA expert here may be able to modify quickly

http://forums.techguy.org/business-applications/658578-combining-worksheets-excel.html


----------

