Excel 2007 Data from many spreadsheets to one

[Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,

I'm trying to make a compilation spreadsheet inside a workbook (Excel 2007).


We have data from 83 different facilities that each have their own spreadsheet tab (this is for a very specific purpose not applicable to my question and it will not be changed).


The headings and formats of the different spreadsheets are 100% identical. The number of rows will vary in each sheet and change pretty constantly.


I would like to combine the data from each spreadsheet onto a single spreadsheet for a summary. I do NOT want to cut and paste every entry from the 83 tables onto the summary sheet.


How can I make excel pull the rows from each sheet and stack them, one on top of the other, on a new spreadsheet?


This is a SHARED workbook and as such, cannot use Advanced Filters or Pivot Tables.

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
You can create a master sheet,
loop thru each sheet
copy one row below header
paste on master sheet
Seeing as there are 83 sheets to go through I DO NOT WANT TO COPY AND PASTE as stated on my original post. This will not be acceptable to management. This will not work when they want the consolidated report as soon as they ask for it. It will create a very long process that I will have to do over and over and over and over again and it is unacceptable.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
I meant all that via code :)