Using Excel VBA to Change the Pivot Table Source - 2 Methods

What Is a Pivot Table in Excel?

A pivot table is a tool through which you can select the data from a dataset and calculate, analyze, summarize, and get data trends.

The dataset contains Product ID, Product, category, sales of the products, Date, and Outlets of a grocery shop.

Method 1 – Using Excel VBA to Change Pivot Table Data in the Same Source Range

Steps:

Select C4:E12.
Select Insert >> Pivot Table in the Toolbar.

In the Pivot Table dialog box, select New Worksheet >> OK.

Select all the categories in the pivot table fields to get a pivot table.
Change the name of the sheet to Pivot Table.

Select Developer >> Visual Basic in the Toolbar or press ALT + F11 to open a VBA window and insert a New Module.

Enter the code in the module.

Code:

Sub ChangePivotTableDataSource()
Dim pivotTable As pivotTable
Dim newRange As Range
Set newRange = Sheets("Dataset").Range("B4:G12")
Set pivotTable = Sheets("Pivot Table").PivotTables("PivotTable1")
' Change the name of the sheets according to _
your sheet name for both newRange and pivot table
pivotTable.ChangePivotCache ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=newRange)
pivotTable.RefreshTable
End Sub

Code Breakdown:

Sub ChangePivotTableDataSource()
Dim pivotTable As pivotTable
Dim newRange As Range
Set newRange = Sheets("Dataset").Range("B4:G12")

In the subroutine ChangePivotTableDataSource, the pivot table is declared as pivotTable and the changed range B4:G12 is declared as Range.

Set pivotTable = Sheets("Pivot Table").PivotTables("PivotTable1")
' Change the name of the sheets according to _
your sheet name for both newRange and pivot table

Sheets(“Pivot Table”) refers to the sheet to execute the process and (PivotTable1) specifies the pivot table.

  pivotTable.ChangePivotCache ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=newRange)
pivotTable.RefreshTable
End Sub

Changes the data source of the dataset and creates new pivot caches. Sets source data range in a new range and ends this program.

Click Run.
Go back to the sheet and check if the program is correctly run. You can create a button:
Select Developer >> Insert >> Button (Icon) in the Toolbar.

Place the button in the sheet.
In the Assign Macro dialog box, select the assigned macro and click OK.

Place the cursor on the button and right-click to see the options menu.
Select Edit Text to change the name of the button.

Note:

You can change the format using Edit Text.

Click the button to see the final output.

If you change data in B4:G12, you will get a new output.

Method 2 – Using Excel VBA to Change Pivot Table Data and the Source Range

A row and a column were added to the dataset.

Steps:

Follow the steps described in Method 1 to open a new Module.
Enter the code.

Code:

Sub ChangePivotTableDataSource2()
Dim pivotTable As pivotTable
Dim newRange As Range
Dim rangeAddress As String
On Error Resume Next
Set newRange = Application.InputBox("Selected Range:", Type:=8)
On Error GoTo 0
If newRange Is Nothing Then
MsgBox "Invalid range", vbExclamation
Exit Sub
End If
'Select sheet name according to your Excel sheet name and add pivot table number according to your Excel sheet as well
Set pivotTable = Sheets("Pivot Table 2").PivotTables("PivotTable2")
pivotTable.ChangePivotCache ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=newRange)
pivotTable.RefreshTable
End Sub

Code Breakdown:

Sub ChangePivotTableDataSource2()
    Dim pivotTable As pivotTable
    Dim newRange As Range
    Dim rangeAddress As String

Declares the subroutine ChangePivotTableDataSource2 in which the pivot table is declared as pivotTable, the new range is declared as Range, and the range address is declared as String.

   On Error Resume Next
    Set newRange = Application.InputBox("Selected Range:", Type:=8)
    On Error GoTo 0
    If newRange Is Nothing Then
        MsgBox "Invalid range", vbExclamation
        Exit Sub
    End If

Selects a new range using the Input Box so that the range will be dynamic. If you input the wrong range, VBA stops the procedure and terminates the program. On Error Go To 0 is used to work with the error and show “Invalid range”.

  pivotTable.ChangePivotCache ActiveWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=newRange)
    pivotTable.RefreshTable
End Sub

Changes the data source of the dataset, creates new pivot caches and ends the program by Setting the source data range in a new range.

Add a button. Follow the steps described in Method 1.
Click the button to select the changed dataset and see the output.

Things to Remember

If the VBA option is blocked, select File >> Option >> Trust Center >> Trust Center settings >> Macro settings >> Enable Macro >> OK.

Using Excel VBA to Change the Pivot Table Source – 2 Methods

What Is a Pivot Table in Excel?

Method 1 – Using Excel VBA to Change Pivot Table Data in the Same Source Range

Method 2 – Using Excel VBA to Change Pivot Table Data and the Source Range

Things to Remember

Related Articles

Leave a reply Cancel reply