Dynamically Change Pivot Table Source Data
This is a handy bit of code which will save you having to update the source range of a pivot table each time new data is added to the table.
In this example I have a data sheet called “Data”.
Each month new row’s are added to the table. Increasing the number of rows.
OR
Overwrite all the data each month, by still increasing the number of rows.
The other sheet I have is called “Pivot” which is where my pivot table is placed.
Added a simple form button – Here is the developer tab starting point on how to add a button
Draw the button size that best suits your needs and click New Macro – Paste this code into the workbook Module
Parts in bold you need to change to match your own scenerio!
Sub ChangePivotDataRange()
‘For Automatically updating a Pivot Table’s data source range
‘Domain: www.modcore.co.uk
Dim Data_sht As Worksheet
Dim Pivot_sht As Worksheet
Dim StartPoint As Range
Dim DataRange As Range
Dim PivotName As String
Dim NewRange As String
‘Set Variables Equal to Data Sheet and Pivot Sheet
Set Data_sht = ThisWorkbook.Worksheets(“Data“)
Set Pivot_sht = ThisWorkbook.Worksheets(“Pivot“)
‘Enter in Pivot Table Name
PivotName = “PivotTable1”
‘Dynamically Retrieve Range Address of Data
Set StartPoint = Data_sht.Range(“A1”)
Set DataRange = Data_sht.Range(StartPoint, StartPoint.SpecialCells(xlLastCell))
NewRange = Data_sht.Name & “!” & _
DataRange.Address(ReferenceStyle:=xlR1C1)
‘Make sure every column in data set has a heading and is not blank (error prevention)
If WorksheetFunction.CountBlank(DataRange.Rows(1)) > 0 Then
MsgBox “One of your data columns has a blank heading.” & vbNewLine _
& “Please fix and re-run!.”, vbCritical, “Column Heading Missing!”
Exit Sub
End If
‘Change Pivot Table Data Source Range Address
Pivot_sht.PivotTables(PivotName).ChangePivotCache _
ThisWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=NewRange)
‘Ensure Pivot Table is Refreshed
Pivot_sht.PivotTables(PivotName).RefreshTable
‘Complete Message
MsgBox PivotName & “‘s data source range has been updated!”
End Sub
Now every time the data changes in your source, just press the button you have made to automatically update the pivot table source range!
Enjoy!