Collecting data from all sheets of the book into one table

Contents

Formulation of the problem

Let’s say that we have a book with a large number of sheets, where each sheet contains a table with data on transactions in this city:

Let’s start with the following considerations:

The structure and columns on all sheets is the same.
The number of lines on all sheets is different.
Sheets may be added or removed in the future.

Our task is to collect all the data from all sheets into one table, so that we can work with it later (filter, sort, build a summary, etc.) There are many ways to do this, but Power Query and macros will probably be the most convenient.

Method 1: Collect data from sheets using Power Query

If you have not yet encountered Power Query in your work, then I strongly advise you to dig in this direction. Using this free add-in, which is already built into Excel by default, can completely turn your entire data collection and analysis process, simplifying everything at times. With the task of collecting data from sheets, Power Query copes very successfully.

Step 1. Connect to the file

To begin with, let’s create a new empty file in Excel, where the data will be collected.

If you have Excel 2010-2013 and have installed Power Query as a separate add-in, then open the tab Power Query, if you have Excel 2016 or newer, then the tab Data (Date). Click the button Get Data/Create Query – From File – Excel Workbook (Get Data / New Query — From file — From Excel) and specify our file with source sheets:

In the window that appears Navigator (Navigator) select any sheet on the left and click on the button in the lower right corner Convert Data (Transform Data) or Change (Edit):

The Power Query Query Editor window should appear, displaying the data from the selected sheet. Since we need, in fact, not one sheet, but all, we will delete all steps in the right panel, except for the first step Source (Source) using the cross to the left of the step name:

What remains after deleting the steps is a list of all objects that Power Query “sees” in external files, and this:

sheets
“smart tables” (Table)
named ranges (Defined Name)
print areas (Print Area), which, in fact, are one of the types of named range

Step 2. Select the desired sheets

The source file can contain a lot of extra things that we don’t need to collect: random unnecessary sheets, service named ranges, side smart tables, etc. It is very important to filter out this “information garbage”, because. in the future, due to any such object, our zapors will most likely crash with an error or incorrectly collect data. Several approaches can be used to solve this problem.

Firstly, you can easily filter the desired objects by type by column Kind. For example, if you only want sheets:

Secondly, if we need only visible sheets, then you can additionally filter by the column Hidden.

Thirdly, if you are know the size of the tablesthat you need, you can easily add a calculated column to our list with a formula that displays the number of columns or rows and then use these numbers for selection. To do this, select on the tab Adding a Column – Custom Column (Add Column — Custom Column) and enter the following formula in the window that opens (case sensitive):

To count the number of rows, you can use a similar function Table.RowCount. The resulting column can then be used to filter “garbage” tables.

Fourth, you can extract the contents of any cell from each sheet (for example, A1) and use it for selection. For example, if there is no word “Product“Then this is not our list. To extract, you will also need to add a calculated column with the following construction:

=[Data][Column1]0 {}

Here:

[Data] – the name of the column, where each cell contains tables with the contents of each sheet (killer wording for the average Excel user, yes, I know)
[Column1] – the name of the column on the sheet from which we want to extract data
0 {} – line number (counting from zero), where we want to get the data from

After filtering the “garbage”, all added auxiliary columns can, of course, be safely removed, leaving only the columns Name и Data.

Step 3. Expand the tables

Now expand the contents of the tables into one whole using the button with double arrows in the column header Databy deselecting the checkbox Use original column name as prefix (Use original column name as prefix):

After clicking on OK Power Query will collect all the data for us in one megatable from all the selected sheets of our file:

It remains only to “bring shine”, namely:

Raise the first row to the table header with the button Use first line as headers (Use first row as headers) tab Home (Home).
Rename first column to City by double clicking on the title.
Remove duplicate table headers that are in the same heap along with the data using a column filter Product.

Everything. It remains only to give our request a suitable name (for example, Assembly) in the panel on the right and then upload the collected data back to Excel with the button close and download tab Home (Home — Close & Load):

In the future, with any changes in the source file, it will be enough just to update our query by right-clicking on the assembled table and selecting the command Update & Save (Refresh) or the same button on the tab Data (Date) or keyboard shortcut Ctrl+Alt+F5.

Pros this approach:

You do not need to be able to program, everything is done quickly and almost without using the keyboard.
The sequence of columns on different sheets may be different – it does not matter, the columns will correctly fit under each other in the final assembly.
You can quickly update the query when the source data changes.

Cons this method:

Only values are collected, i.e. formulas from source sheets are not saved.
Column names must be the same on all sheets, up to case.
It is impossible to choose which range is taken from each sheet – this is determined automatically (everything is taken).
Upgrading requires Excel 2016 or later or the Power Query add-in installed.

Method 2. Collecting data from sheets with a macro in VBA

A similar result can be achieved using a more “classic” approach – a macro in VBA. To do this, on the tab developer (Developer) press the button Visual Basic or use keyboard shortcut Alt+F11. In the window that opens, add a new module through the menu Insert – Module and copy the text of this macro there:

Sub CollectDataFromAllSheets() Dim ws As Worksheet Set wbCurrent = ActiveWorkbook Workbooks.Add Set wbReport = ActiveWorkbook .Worksheets(1).Range("A1") 'loop through all sheets of the source file For Each ws In wbCurrent.Worksheets 'determine the number of the last line on the current sheet and on the assembly sheet n = wbReport.Worksheets(1).Range ("A1").CurrentRegion.Rows.Count 'set the initial range to be copied from each sheet - any option to choose from: Set rngData = ws.Range("A1:D1") 'fixed range or Set rngData = ws. UsedRange 'everything on the sheet or Set rngData = ws.Range("F1").CurrentRegion 'area starting from cell F1 or Set rngData = ws.Range("A5", ws.Range("A5"). SpecialCells(xlCellTypeLastCell)) 'from A5 to the end of the sheet 'copy the source range and paste it into the final book from the following line rngData.Copy Destination:=wbReport.Work sheets(2).Cells(n + 2, 2) Next ws End Sub

You can run the created macro on the tab developer button Macros (Developer — Macros) or with a keyboard shortcut Alt+F8. The macro will automatically create a new book and copy the data you need there.