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.
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!
‘For Automatically updating a Pivot Table’s data source range
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 & “!” & _
‘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!”
‘Change Pivot Table Data Source Range Address
‘Ensure Pivot Table is Refreshed
MsgBox PivotName & “‘s data source range has been updated!”
Now every time the data changes in your source, just press the button you have made to automatically update the pivot table source range!