Sub Update Pivot Table Range() Dim Data_Sheet As Worksheet Dim Pivot_Sheet As Worksheet Dim Start Point As Range Dim Data Range As Range Dim Pivot Name As String Dim New Range As String Dim Last Col As Long Dim last Row As Long 'Set Pivot Table & Source Worksheet Set Data_Sheet = This Workbook.
window appears, change the Table/Range value to reflect the new data source for your pivot table. Now when you return to your pivot table, it should display information from the new data source.You can use Excel VBA to automatically update a pivot table when its worksheet is activated.There are instructions on my Contextures website, for copying VBA code to your own files.In above formula, I have used offset function to create a dynamic range. Address(Reference Style:=xl R1C1) 'Change Pivot Table Data Source Range Address Pivot_Sheet. I have mentioned cell A1 as the starting point and then without mentioning rows and columns, I have specified the height and width of the range by using COUNTA Function. Range(Start Point, Cells(Down Cell, Last Col)) New Range = Data_Sheet. Today, in this post, I’d like to share with you three different methods which we can use to update the source range of a pivot table automatically. By applying a table in source data you don’t need to change the source range of your pivot table again and again. Every time you run this macro it will create a new dynamic range.