Consolidating worksheets in excel
I have a workbook with over 1400 worksheets (tabs) and am looking for a formula, VBA code, or any other way that will allow me to collect the data from ALL worksheets in one table. Each worksheet name is not sequential but do seem to be in chronological order4.Each worksheet is set up the exact same layout (cells) for all information EXCEPT Questions. This worked great and I could easily set up formulas to create a table.The “FROM” portion tells Microsoft Query where to find the data.
The ranges you consolidate do not have to be of the same size in each worksheet, the number of rows or columns might be different from sheet to sheet.The trouble is, working with Data Consolidation can be tricky! Data Consolidation is an incredibly useful tool that help you understand and present your data quickly and easily.The screen may look daunting but like all great things, good planning is the key.This example contains some tips from Edmund, one of our Excel experts, and will help you decide how to organise your data before you start summarising it to get easily understandable results.Here we look at: NOTE: The following processes will work in Excel versions: 2007, 2010, 20.