Changing the data source of a PivotTable using VBA can be a handy skill when dealing with dynamic datasets or automating report updates in Excel. Here’s a step-by-step guide on how to do this:
Prerequisites
- You have Microsoft Excel installed.
- You have a workbook with a PivotTable already created.
Step-by-Step Guide
Sub ChangePivotTableDataSource()
Dim ws As Worksheet
Dim pt As PivotTable
Dim newDataSource As String
' Set the worksheet containing the PivotTable
Set ws = ThisWorkbook.Sheets("Sheet1") ' Change as needed
' Set the PivotTable
Set pt = ws.PivotTables("PivotTable1") ' Change as needed
' Set the new data source (range address or named range)
newDataSource = "NewDataRange" ' Change as needed
' Change the PivotTable's data source
pt.ChangePivotCache ThisWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=newDataSource)
' Refresh the PivotTable
pt.RefreshTable
End Sub
- Open VBA Editor:
- Press `ALT` + `F11` to open the VBA editor.
- Locate Your Workbook’s Module:
- In the Project Explorer on the left, find your workbook and select it.
- If you don’t already have a module, you can insert one by clicking `Insert` > `Module`.
- Write the VBA Code:
- Add the following code to the module. This example assumes that the PivotTable is on `Sheet1` and you are changing it to use a named range called `NewDataRange` in the workbook.
Key Points
- `ws` and `pt`: These variables refer to the worksheet and PivotTable objects. Adjust the sheet name and PivotTable name according to your workbook.
- `newDataSource`: This refers to the new data source for your PivotTable. It can be an Excel range address (e.g., `”Sheet2!A1:D100″`) or a named range.
- PivotCache: The `ChangePivotCache` method is used to change the data source of the PivotTable. The PivotCache must be a new one created with the specified `SourceData`.
- `RefreshTable`: This ensures the PivotTable updates to reflect the new data source.
- Run the Macro:
- Close the VBA editor and return to Excel.
- Press `ALT` + `F8` to open the “Macro” dialog box.
- Select `ChangePivotTableDataSource` and click `Run`.
Notes
- Ensure that the new data source has the required headers and is formatted consistently with what the PivotTable expects.
- Adjust the sheet name, PivotTable name, and data source range or name in the code to match your specific requirements.
- Always make a backup of your workbook before running VBA scripts to prevent accidental data loss.
This VBA script will dynamically update the PivotTable’s data source to the specified range or named range, allowing efficient management of data-driven reports.
Unlock Your Potential
Excel
Access
Power BI
