VBA Code to Create Pivot Table with Dynamic Range in Excel

Get FREE Advanced Excel Exercises with Solutions!

In this article, you will find a way to create a pivot table with dynamic range using VBA code in Excel. You can make sure that your pivot tables respond to changes in your data by automating the process. The article explains the code in detail. In the end, you’ll be able to effectively use VBA to create a pivot table with dynamic ranges.


Excel VBA Code to Create Pivot Table with Dynamic Range: Step-by-Step Procedures

You can fully utilize Excel pivot tables if you give them the ability to dynamically adapt and update as your data changes. In this article, we’ll explain how to use VBA code to create a pivot table with a dynamic range in 2 simple steps. You can automate the procedure and make sure your pivot table always displays the most recent data by utilizing the flexibility of VBA and defining a dynamic range. We will use a sample dataset containing First Name, Last Name, Email, and Gross Pay for the demonstration of creating a pivot table with dynamic range.

A sample dataset


⦿ Step 1: Creating a Pivot Table with VBA Macro

As the main focus of this article is to make a pivot table dynamic, you need to start with a pivot table. So, this step will show the easy task of creating a pivot table in Excel.

To insert a pivot table, you can do it manually by selecting the data range>> Insert tab>> Pivot Table>>From Table/Range or you can do the same thing using VBA. We’ll look at automating pivot table creation in Excel using VBA (Visual Basic for Applications) in this blog post. You can dynamically create pivot tables by using VBA code based on your data, making analysis and visualization simple.

  • Amazingly, you can create a pivot table without going to Insert>>Pivot Table. To do that, you have to create a sample data sheet named “Data” and your pivot table will be created in the sheet “Pivot” after running the following VBA code in the module of a VBA.
  • Also, modify your code as the following VBA code presumes that the data in the “Data” sheet starts from B4.
  • The pivot table will be created in the sheet named “Pivot” where the table will start from A1.
Sub CreatePivotTable()
Dim pivotSheet As Worksheet, dataSheet As Worksheet
Dim pivotCache As PivotCache
Dim pivotTable As PivotTable
Dim dataRange As Range
Dim lastRow As Long, lastCol As Long
Dim sheet As Variant
On Error Resume Next
With Application
.DisplayAlerts = False
.ScreenUpdating = False
End With
For Each sheet In ActiveWorkbook.Worksheets
If sheet.Name = "Pivot" Then
sheet.Delete
End If
Next sheet
Worksheets.Add.Name = "Pivot"
Set pivotSheet = Worksheets("Pivot")
Set dataSheet = Worksheets("Data")
lastRow = dataSheet.Cells(dataSheet.Rows.Count, 2).End(xlUp).Row
lastCol = dataSheet.Cells(4, dataSheet.Columns.Count).End(xlToLeft).Column
Set dataRange = dataSheet.Range("B4").Resize(lastRow - 3, lastCol - 1)
Set pivotCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=dataRange).CreatePivotTable(TableDestination:=pivotSheet.Range("A1"), TableName:="SalesPivotTable")
Set pivotTable = pivotCache.CreatePivotTable(TableDestination:=pivotSheet.Range("B4"), TableName:="SalesPivotTable")
With pivotSheet.PivotTables("SalesPivotTable").PivotFields("Region")
.Orientation = xlPageField
End With
With pivotSheet.PivotTables("SalesPivotTable").PivotFields("Sub-Category")
.Orientation = xlRowField
End With
With pivotSheet.PivotTables("SalesPivotTable").PivotFields("State")
.Orientation = xlColumnField
End With
With pivotSheet.PivotTables("SalesPivotTable").PivotFields("Sales")
.Orientation = xlDataField
.Function = xlSum
End With
End Sub

Entering the VBA code in the Module

  • Finally, this is what the final output will look like in the following video.

Read More: How to Use VBA to Create Pivot Table from Named Range in Excel


⦿ Step 2: Using VBA to Create Pivot Table With Dynamic Range

Your pivot tables will automatically adapt to changes in your dataset by changing the source data range, providing accurate and current analysis. In this step of the article, you are going to learn how to implement this VBA technique for making pivot tables with dynamic ranges in Excel to improve your data analysis and save time

VBA Code:

Sub RefreshExistingPivotTable()
Dim ws As Worksheet
Dim pt As PivotTable
Dim pc As PivotCache
Set ws = ThisWorkbook.Worksheets("Sheet5") ' Replace "Sheet5" with the name of the sheet containing the pivot table
' Check if a pivot table already exists in the worksheet
If ws.PivotTables.Count > 0 Then
' Assuming only one pivot table exists, you can use the first item in the PivotTables collection
Set pt = ws.PivotTables(1)
' Get the existing pivot table's PivotCache
Set pc = pt.PivotCache
' Modify the source data range of the existing pivot table's PivotCache
pc.SourceData = ThisWorkbook.Worksheets("Sample Data").Range("B4:E14").CurrentRegion.Address(True, True, xlR1C1, True)
' Refresh the pivot table
pt.RefreshTable
Else
MsgBox "No existing pivot table found.", vbCritical
End If
End Sub

Entering the VBA code in the Module

VBA Explanation:

Sub RefreshExistingPivotTable()
  • It declares a subroutine named RefreshExistingPivotTable.
Dim ws As Worksheet
    Dim pt As PivotTable
    Dim pc As PivotCache
  • The above three lines with Dim declare three variables. ws is used for storing a Worksheet object, pt for storing a pivot table object, and pc for storing a Pivot Cache object.
Set ws = ThisWorkbook.Worksheets("Sheet5") ' Replace "Sheet5" with the name of the sheet containing the pivot table
  • While demonstrating the problem, we used a pivot table in Sheet5. Now this line of VBA code sets the ws variable for the reference to the worksheet named Sheet5.
If ws.PivotTables.Count > 0 Then
  • This line determines whether any pivot tables are present in the worksheet that the “ws” variable refers to.
Set pt = ws.PivotTables(1)
  • This line sets the “pt” variable to refer to the first pivot table in the worksheet’s PivotTables collection if there are at least one or more pivot tables.
Set pc = pt.PivotCache
  • Next, this line configures the “pc” variable to refer to the PivotCache associated with the pivot table.
pc.SourceData = ThisWorkbook.Worksheets("Sample Data").Range("B4:E14").CurrentRegion.Address(True, True, xlR1C1, True)
  • Then, this line modifies the PivotCache of the existing pivot table. It changes the range “B4:E14” in the worksheet titled “Sample Data” of the workbook where the VBA code is being executed as the SourceData property of the “pc” PivotCache object. You can change “Sample Data” to the actual name of the worksheet that houses the pivot table’s new data range.
pt.RefreshTable
  • This line calls the RefreshTable method on the “pt” PivotTable object to update the pivot table.
Else
MsgBox "No existing pivot table found.", vbCritical
End If
  • This block shows a message box with the text “No existing pivot table found.” and a warning icon if the worksheet has no pivot tables.
End Sub
  • The subroutine ends with this line.

  • Finally, this is what the final output will look like in the following video.

Read More: How to Insert A Pivot Table in Excel


Things to Remember

  1. Do not forget to replace the name of your sheet in the VBA code. In that code, “Data” is the name of the sheet. From that sheet, the VBA code takes the range to make a pivot table.
  2. Also, remember that the pivot will be placed on the “Pivot” sheet. Here, we have used two VBA codes, one for creating a pivot table and another for filtering that table.

Frequently Asked Questions (FAQs)

  • Can I modify the code to use a different range for my pivot table?

Yes, you can change the worksheet name and range references to reflect the desired range.

  • What are the benefits of using a dynamic range for pivot tables?

By using a dynamic range, you can avoid manually updating the source range. Also, it will make your pivot table adapt as your data changes.

  • When using dynamic ranges with VBA code, are there any restrictions or things to keep in mind?

The dynamic range formula must be precise, large datasets must be handled effectively. Additionally, you must check the performance impact of frequent updates to the dynamic range.


Download Practice Workbook

You can download the practice workbook from the following download button.


Conclusion

To sum up, You can automate and modify your data analysis process by using VBA code to create pivot tables with dynamic ranges. By specifying a dynamic range, you can save time and effort by having your pivot tables adapt automatically as your data changes. You can quickly create and customize pivot tables based on dynamic ranges using the provided VBA code and techniques illustrated here. With this information, you can maximize the use of pivot tables and improve Excel’s data analysis features.


<< Go Back to How to Create Pivot Table in Excel | Pivot Table in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Al Ikram Amit
Al Ikram Amit

Al Ikram Amit, holding a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a key Excel & VBA Content Developer at ExcelDemy. Driven by a profound passion for research and innovation, he actively immerses himself in Excel. In his role, Amit not only skillfully addresses complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his steadfast commitment to consistently delivering exceptional, high-quality content that adds significant... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo