Learn How to Automatically Create Pivot Tables in Excel With VBA
Free to use
Pivot tables in Excel are pivotal in making data easier to understand and comprehend. A pivot table can condense and crunch data into meaningful structures. MS Excel users have widely adopted them within the data industry.
Did you know you can automate your pivot tables in Excel and create them with a single click? MS Excel integrates well with VBA and has become an excellent tool for automating repetitive tasks.
Here’s how you can automate a pivot table with a macro in MS Excel VBA.
Use a Practice Data Set
You can download and use a dummydataset from Tableauto follow the VBA script within this guide. The VBA code will work with any other dataset, with a few basic tweaks. Before starting, do ensure you haveenabled macros within your Excel workbook.
There are a few essential columns that you can use within the pivot table. To understand the table’s nuances and final structure, you cancreate a basic pivot table manuallywith the following elements:
The end pivot should look as follows:
However, it’s possible to let VBA do it automatically instead of preparing it manually.
How to Automatically Create Pivot Tables in Excel
To automate your pivot tables with VBA, open a new Excel file, and rename the sheets as follows:
TheMacrosheet contains the macro script, while theDatasheet contains your data. On the macro sheet, you can insert any shape of your choice and assign the macro to it. Right-click on the shape, and click on Assign Macro.
In the following dialog box, click on the name of your macro and click Ok. This step assigns the macro to the shape.
1. Open the Excel VBA Coding Editor
PressAlt + F11to open the code editor. Once you’re in the code editor, right-click the file name, followed byInsertandModule. It’s important to remember that you will write all VBA code within a module before executing it.
It’s a good practice to use a module name that resonates with the purpose of the code. Since this is a demo, it’s possible to define the module name as follows:
The module name ends withEnd Sub, which is the ending command of a module:
2. Declare Variables
Within the module, start by declaring variables to store some user-defined values you’ll use in the script. you could use theDimstatement to declare variables, as follows:
You’ll use these variables for the following:
3. Suppress Warnings and Messages
Unnecessary errors, warnings, and messages slow down your VBA codes. By suppressing such messages, you may speed up the process considerably.
Use the following code:
When this code runs, it suppresses any alerts, warnings, and messages Excel would show otherwise. You can turn off DisplayAlerts and ScreenUpdating parameters by setting their values toFalse.
Towards the end of the code, you can turn them on again by setting the value asTrue.
4. Delete Any Existing Pivot Sheets
To create a new pivot table, you have two options. Firstly, delete the existing pivot sheet and use VBA to create a new sheet to store the pivot. Alternatively, you may use an existing worksheet to hold the pivot.
In this guide, let’s create a new pivot sheet to store the pivot table.
Thefor eachloop cycles through each sheet within the workbook and stores the sheet name within thesht1variable. You can use any variable name (sht1) to hold the sheet name. The loop cycles through every sheet within the current workbook, looking for one with the specific name (Pivot).
When the sheet name matches, it deletes the sheet and moves to the next sheet. Once the code checks all the sheets, it exits the loop and moves to the next part of the code, which adds a new sheet,Pivot.
Here’s how you’re able to do it:
5. Define Data Source and Pivot Sheets
It’s essential to create variables to store the references of the Pivot and Data sheets. These work as shortcuts, which you can reference throughout the rest of the code.
6. Identify the Last Used Row and Column
This part of the code works dynamically, as it sizes up the last populated row and column within the data.
7. Create a Pivot Cache and a Pivot Table
A pivot cache holds the pivot table; therefore, you need to create a cache before creating a pivot table. You must use VBA’s syntax references to make the pivot cache within the Pivot sheet.
By referencing the pivot cache, you need to create a pivot table. As a part of the pivot table, you can define the sheet, the cell reference, and the name of the pivot table.
8. Insert Rows, Columns, Filters, and Values
Since the pivot table is ready, you need to start adding the parameters within the filters, rows, columns, and aggregation values. You can use the VBApivotfieldscommand to start declaring the details.
To Add Filter Values:
To Add Row Values:
To Add Column Values:
To Add Aggregation Values:
It’s essential to note that you must reference the activesheet (Pivot sheet), followed by the pivot table name and the variable name. When you need to add the filter(s), row(s), and column(s), you can toggle between various syntax, which includes the following:
Finally, you may use thexlDataFieldcommand to calculate the value aggregations. You can use other aggregate functions like xlSum, xlAverage, xlCount, xlMax, xlMin, and xlProduct.
9. Running the Excel VBA Code to Create Automatic Pivots
Finally, when the entire program is ready, you’re able to run it by pressingF5or clicking theplaybutton. When you go back to the Pivot sheet in your workbook, you will see that a new pivot table is ready for you to review.
If you want to see a step-by-step execution of how the code command plays out line by line, you can navigate to the code editor and pressF8several times. This way, you can see how each line of code works and how VBA creates your pivots automatically.
Learning to Code Pivot Tables Automatically
Pivots are not restricted to MS Excel only. Programming languages like Python let you create optimized pivots with just a few lines of code.
Data optimization can’t be easier than this. you could effectively pick and choose your commands in Python and accomplish a similar Excel-like pivot structure with ease.
Go beyond Microsoft Excel and learn how to create pivot tablets with Python and just a few lines of code.
I gripped my chair the entire time—and then kept thinking about it when the screen turned off.
Obsidian finally feels complete.
It saves me hours and keeps my sanity intact.
Make sure you don’t miss these movies and shows before Netflix removes them.
Not all true crime is about hacking, slashing, and gore.
