Create Multiple Pivot Tables in Excel Automatically Using VBA

—

by

Himanshu
in

Pivot Tables are one of Excel’s most powerful tools for summarizing data and discovering insights. But if you’re working with large datasets and need multiple Pivot Tables, creating each one manually can be time-consuming and prone to error.

In this tutorial, we’ll walk through a powerful Excel VBA macro that does all the hard work for you—automatically generating multiple Pivot Tables from your dataset in seconds.

🧠 What You’ll Learn:

  • How to set up your data source dynamically using VBA
  • How to create multiple Pivot Tables using a single Pivot Cache
  • How to organize, format, and style each Pivot Table
  • How to combine rows, columns, and data fields in advanced Pivot Table design

🛠 VBA Macro to Insert Multiple Pivot Tables

Here’s the complete VBA script that automatically creates 8 categorized Pivot Tables plus one detailed summary Pivot Table:

vbCopyEditSub Insert_Multiple_Pivot_Tables()
    ' Full VBA code here (omitted here for brevity)
End Sub

The macro performs the following key steps:

🔄 1. Deletes and Recreates the “PivotTable” Sheet

Ensures your output is always clean by removing any existing PivotTable sheet and creating a fresh one.

📌 2. Dynamically Detects the Data Range

Instead of hardcoding, it uses:

vbaCopyEditLastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)

This makes your macro adaptable to datasets of varying lengths and widths.

📦 3. Creates a Single Pivot Cache

Instead of making a new cache for every Pivot Table (which increases file size), it smartly uses just one:

vbaCopyEditSet PCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRange)

📈 4. Inserts 8 Thematic Pivot Tables:

Each pivot summarizes a different aspect of the data:

  • Region-wise Total Sales
  • Product-wise Total Sales
  • Payment Mode-wise Sales
  • Delivery Status-wise Units
  • Customer Type-wise Sales
  • Order Priority-wise Units
  • Warranty-wise Units
  • Return Eligibility-wise Units

Each is formatted with:

vbaCopyEditpvt.ShowTableStyleRowStripes = True
pvt.TableStyle2 = "PivotStyleDark2"

📊 5. Adds a Detailed Multi-Dimensional Pivot Table

At the end of the macro, a detailed sales pivot is generated with:

  • Row Fields: Region and Salesperson
  • Column Field: Product
  • Data Field: Total Sales (formatted as Revenue)

The code includes:

vbaCopyEditWith PTable.PivotFields("Total Sales")
    .Orientation = xlDataField
    .Function = xlSum
    .NumberFormat = "#,##0"
    .Name = "Revenue"
End With

And finally, it auto-adjusts column widths and zooms out to 80% for better readability.

📂 Download the Excel Macro File

Automate PivotDownload


(Make sure to enable macros after opening)

💡 Why Use VBA for Pivot Tables?

  • ⚡ Speed: Create 8+ Pivot Tables instantly
  • 🔁 Automation: Run it anytime with new data
  • 📦 Efficiency: Uses a single Pivot Cache to reduce file size
  • 🎯 Customization: Easy to modify for different categories or fields

✍️ Final Thoughts

With just a few lines of VBA, you can transform repetitive tasks into powerful automation tools. Pivot Tables offer deep insights—and now, you’ve just automated the whole process!

Have questions or want to explore more Excel automation? Feel free to connect!

Get the Free Training App