Splitting a table into sheets

Microsoft Excel has many tools for collecting data from several tables (from different sheets or from different files): direct links, function INDIRECT (INDIRECT), Power Query and Power Pivot add-ons, etc. From this side of the barricade, everything looks good.

But if you run into an inverse problem – spreading data from one table to different sheets – then everything will be much sadder. At the moment, there are no civilized built-in tools for such data separation in the arsenal of Excel, unfortunately. So you will have to use a macro in Visual Basic, or use the macro recorder + Power Query combination with a little “file refinement” after.

Let’s take a closer look at how this can be implemented.

Formulation of the problem

We have as initial data such a table with a size of more than 5000 rows for sales:

Task: to distribute the data from this table by city on separate sheets of this book. Those. at the output, you need to get on each sheet only those rows from the table where the sale was in the corresponding city:

Prepare

In order not to complicate the macro code and make it as easy to understand as possible, let’s perform a couple of preparatory steps.

First, the create a separate lookup table, where a single column will list all the cities for which you want to create separate sheets. Of course, this directory may not contain all the cities present in the source data, but only those for which we need reports. The easiest way to create such a table is to use the command Data – Remove Duplicates (Data — Remove duplicates) for column copy City or function UNIK (UNIQUE) – if you have the latest version of Excel 365.

Since new sheets in Excel are created by default before (to the left of) the current (previous) one, it also makes sense to sort the cities in this directory in descending order (from Z to A) – then after creation, the city sheets will be arranged alphabetically.

Second, the пconvert both tables to dynamic (“smart”) to make it easier to work with them. We use the command Home – Format as a table (Home — Format as Table) or keyboard shortcut Ctrl+T. On the tab that appears Constructor (Design) let’s call them tablProdaji и TableCity, respectively:

Method 1. Macro for division by sheets

On the Advanced tab developer (Developer) click on the button Visual Basic or use keyboard shortcut Alt+F11. In the macro editor window that opens, insert a new empty module through the menu Insert – Module and copy the following code there:

Sub Splitter()      For Each cell In Range("таблГорода")          Range("таблПродажи").AutoFilter Field:=3, Criteria1:=cell.Value          Range("таблПродажи[#All]").SpecialCells(xlCellTypeVisible).Copy          Sheets.Add          ActiveSheet.Paste          ActiveSheet.Name = cell.Value          ActiveSheet.UsedRange.Columns.AutoFit      Next cell      Worksheets("Данные").ShowAllData  End Sub	  

Here with a loop For Each … Next implemented the passage through the cells of the directory TableCity, where for each city it is filtered (method AutoFilter) in the original sales table and then copying the results to the newly created sheet. Along the way, the created sheet is renamed to the same name of the city and auto-fitting the width of the columns for beauty is turned on on it.

You can run the created macro in Excel on the tab developer button Macros (Developer — Macros) or keyboard shortcut Alt+F8.

Method 2. Create multiple queries in Power Query

The previous method, for all its compactness and simplicity, has a significant drawback – the sheets created by the macro are not updated when changes are made in the original sales table. If updating on the fly is necessary, then you will have to use the VBA + Power Query bundle, or rather, create using a macro not just sheets with static data, but updated Power Query queries.

The macro in this case is partially similar to the previous one (it also has a cycle For Each … Next to iterate over cities in the directory), but inside the loop there will no longer be filtering and copying, but creating a Power Query query and uploading its results to a new sheet:

Sub Splitter2() For Each cell In Range("City table") ActiveWorkbook.Queries.Add Name:=cell.Value, Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Source = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Content]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Source, {{""Category"", type text}, {""Name"", type text}, {""City"", type text}, {""Manager"", type text}, {""Deal date "", type datetime}, {""Cost"", type number}})," & Chr(13) & "" & Chr(10) & " #""Rows with filter applied"" = Table.Se" & _ "lectRows(#""Changed type"", each ([City] = """ & cell.Value & """))" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Rows with filter applied""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB;Provider =Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & cell.Value & ";Extended Properties=""""" _ , Destination:=Range("$A$1")).QueryTable .CommandType = xlCmd Sql .CommandText = Array("SELECT * FROM [" & cell.Value & "]") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False . SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=False End With ActiveSheet.Name = cell.Value Next cell End Sub  

After its launch, we will see the same sheets by city, but already created Power Query queries will form them:

With any changes in the source data, it will be enough to update the corresponding table with the right mouse button – the command Update & Save (Refresh) or update all cities at once in bulk using the button Update All tab Data (Data — Refresh All).

• What are macros, how to create and use them
• Saving workbook sheets as separate files
• Collecting data from all sheets of the book into one table