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

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

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

• 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.

• 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

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

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

• 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.

Download Practice Workbook

Download the practice sheet here.

Related Articles

• How to Hide Source Data in Excel Pivot Table
• Pivot Table is Not Picking up Data in Excel

<< Go Back to Pivot Table Data Source | Pivot Table in Excel | Learn Excel