Excel VBA to Change Pivot Table Source (2 Easy Ways)

Suppose you need to work with a single criterion in a huge dataset. In that case, we use the pivot table to narrow down other criteria, but what if you already selected the criteria and now you have to change them? Starting the full process from the beginning is not an efficient way to do the work. So, you can use Microsoft Excel VBA to change the pivot table source. In this article, we will learn how to change pivot table data with a source using Excel VBA. So, let’s start.

The above overview video shows the output after changing the pivot table source. You will get appropriate and proper information once you go through the entire article.

What Is Pivot Table in Excel?

A pivot table is a tool in Excel where you can select the required data from a dataset and calculate, analyze, summarize, and get the data trend for better understanding. Pivot table charts are a visualization of the dataset summary. For instance, a company document contains cost, sales, and profit in one file, but you need to work on profit only. Here, you can create a pivot table with profit and work without the other hassles.

Excel VBA to Change Pivot Table Source: 2 Easy Ways

The dataset below represents the Product ID, Product, category, sales of the products, Date, and Outlets of the grocery shop, which is named ABC Groceries. This dataset shows the one-day average sale of a particular product in different outlets. Now, we are going to set this information in a pivot table so that we can get only the required information.

Sometimes, we need to change the source of the pivot table. So, follow the below methods to change the pivot table source using Excel VBA. Firstly, you need to open the VBA module. If you don’t know how to open the VBA editor, go through this article Open VBA window and insert New Module.

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

In this method, we will show you how to change pivot table data in the same source range. Here, the changed data will be fixed, and once you run the program, you cannot simply change the range that you already changed. To execute this simple method, follow the below steps.

📌 Steps:

• Initially, select cell range C4:E12 from the dataset.
• Therefore, select Insert >> Pivot Table from the Toolbar to get the Pivot Table dialog box.

• Select New Worksheet >> OK from the dialog box to get a new sheet.

• After that, select all the categories from the pivot table fields to get a pivot table with the required information.
• Change the name of the sheet to Pivot Table to avoid confusion during programming.

• Now, select Developer >> Visual Basic from the Toolbar or press ALT + F11 from the keyboard to get the Visual Basic module.

• Consequently, write down the code in the module as below.

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

Once you complete the code, you can run the code and complete this procedure, but after clicking Run you need to go back to the sheet and check if the program runs correctly or not. To avoid this hassle, you can create a button with a simple click. Now act according to the following steps to add a button.

• In the beginning, select Developer >> Insert >> Button (Icon) from the Toolbar.

• Place the button in a random place, and the Assign Macro dialog box will pop up as below.
• Now, select the assigned macro and click OK.

• After assigning a macro, place the cursor on the button and right-click the mouse to get the option menu.
• Select Edit Text to change the button name.

• Finally, click the button to get the final output as below.

Now, if you change the data from B4:G12, you will get the new output as below.

2. Using Excel VBA to Change Pivot Table Data for Changing Source Range

In this method, we will change the pivot table data to change the source range. Let’s change the dataset (Add a row) as below. Here we add a new column, which we will add to the pivot table as well. Now, go through the below steps to execute this process.

📌 Steps:

• Firstly, complete the procedure till write down the code applying the same method already shown in the previous method.
• Then write down the code in the Excel VBA module as below.

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

• Therefore, add a button using the procedure already shown in the previous method.
• Finally, click the button to select the changed dataset and get the new output as below.

Things to Remember

• While working on Excel make sure your Excel supports VBA. If the VBA option is blocked then select File >> Option >> Trust Center >> Trust Center settings >> Macro settings >> Enable Macro >> OK.
• You can also add different criteria column-wise while working on changing the dataset range.

Download Practice Workbook

You can find the practice sheet here.

Conclusion

In this article, we learned how to change pivot table data from a source using Excel VBA. Here, we showed two different ways to solve this problem and covered everything regarding this issue. Hopefully, you can solve the problem shown in this article.

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