VBA Code to Create Pivot Table with Dynamic Range in Excel

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.

⦿ 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

• 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

• First, you have to launch the VBA editor. Click here to see how to launch VBA and insert a Module in Microsoft Excel.
• After launching VBA, insert a module from the Insert Then copy the code below and paste it into the code window.

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

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

If ws.PivotTables.Count > 0 Then

Set pt = ws.PivotTables(1)

Set pc = pt.PivotCache

pc.SourceData = ThisWorkbook.Worksheets("Sample Data").Range("B4:E14").CurrentRegion.Address(True, True, xlR1C1, True)

pt.RefreshTable

Else
MsgBox "No existing pivot table found.", vbCritical
End If

End Sub

• 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