Dynamic Pivot Table Range that Don’t Needs to Update

- Written by Puneet Gogia

Contents hide

Apply Table to have an Auto-Updating Pivot Table Range

Convert Data into a Table Before Creating a Pivot Table

Convert Data into a Table After Creating a Pivot Table

Create a Dynamic Pivot Table Range with OFFSET Function

How does this Formula Work?

Update the Pivot Table using a VBA Code

Get the Excel File

Apply Table to have an Auto-Updating Pivot Table Range

A few days back I asked John Michaloudis about his million-dollar pivot table advice. He says: Put your source data in a table. Believe me, it’s million-dollar advice.

By applying a table in source data, you don’t need to change the source range of your pivot table again and again.

Whenever you add new data, it will automatically update the pivot table range.

Convert Data into a Table Before Creating a Pivot Table

Every time before creating a pivot table make sure to apply the table to source data by using the following steps.

Select any of the cells in your data.
Use shortcut key Control + T or Go to → Insert Tab → Tables → Table.
You will get a pop-up window with your current data range.
Click OK.
Now to create a pivot table select any cell of your data. Go to → Design Tab → Tools → Summarize With Pivot Table.
Click OK.

Now, whenever you add new data to your datasheet it will automatically update the pivot table range and you just have to refresh your pivot table.

Convert Data into a Table After Creating a Pivot Table

If you already have a pivot table in your worksheet you can use the following steps to convert your data source into a table.

Select any of the cells in your data source.
Use shortcut key Control + T or Go to → Insert Tab → Tables → Table.
You will get a pop-up window with your current data range.
Click OK.
Now, select any of cells from your pivot table and Go to → Analyze → Data → Change Data Source → Change Data Source (Drop Down Menu).
You will get a pop-up window to re-select your data source or you can also enter the name of the table into the range input.
Click OK.

From now onwards every time when you add new data into your source sheet it will increase the pivot table range to automatically update it.

Create a Dynamic Pivot Table Range with OFFSET Function

The other best way to update the pivot table range automatically is to use a dynamic range.

Dynamic range can expand automatically whenever you add new data to your source sheet. Following are the steps to create a dynamic range.

Go to → Formulas Tab → Defined Names → Name Manager.
Once you click on name manager you will get a pop-up window.
In your name manager window click on new to create a named range.
In your new name window, enter
1. A name for your new range. I am using the name “SourceData”.
2. Specify the scope of the range. You can specify between the current worksheet or workbook.
3. Add a comment to describe your named range. Enter the below formula to “Refer to” input bar.
```
=OFFSET(PivotTableData!$A$1,0,0,COUNTA(PivotTableData!$A:$A),COUNTA(PivotTableData!$1:$1))
```
In the end, click OK.

create a new name to update pivot table range

Now, you have a dynamic range to create a pivot table.

All you have to do is just create a pivot table with your source data and after that change the source with the named range using the same method which I have used in the first method of tables.

Once you add new data to your source sheet, just refresh your pivot table.

How does this Formula Work?

In the above formula, I have used the offset function to create a dynamic range.

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.

COUNTA will count the cells with values from column A and row 1 and tell offset to expand its height and width accordingly.

The only thing you have to take care that there should be no blank cell in between column A and row 1.

Update the Pivot Table using a VBA Code

Most people love to use VBA codes. So here is the code to use to update the pivot table range with VBA.

Sub UpdatePivotTableRange()

Dim Data_Sheet As Worksheet
Dim Pivot_Sheet As Worksheet
Dim StartPoint As Range
Dim DataRange As Range
Dim PivotName As String
Dim NewRange As String
Dim LastCol As Long
Dim lastRow As Long

'Set Pivot Table & Source Worksheet
Set Data_Sheet = ThisWorkbook.Worksheets("PivotTableData3")
Set Pivot_Sheet = ThisWorkbook.Worksheets("Pivot3")

'Enter in Pivot Table Name
PivotName = "PivotTable2"

'Defining Staring Point & Dynamic Range
Data_Sheet.Activate
Set StartPoint = Data_Sheet.Range("A1")
LastCol = StartPoint.End(xlToRight).Column
DownCell = StartPoint.End(xlDown).Row
Set DataRange = Data_Sheet.Range(StartPoint, Cells(DownCell, LastCol))
NewRange = Data_Sheet.Name & "!" & DataRange.Address(ReferenceStyle:=xlR1C1)

'Change Pivot Table Data Source Range Address
Pivot_Sheet.PivotTables(PivotName). _
ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:=NewRange)

'Ensure Pivot Table is Refreshed
Pivot_Sheet.PivotTables(PivotName).RefreshTable

'Complete Message
Pivot_Sheet.Activate
MsgBox "Your Pivot Table is now updated."

End Sub

Things you have to change before you use it in your workbook.

Line13: Change the name of the source worksheet.
Line14: Change the name of the pivot table sheet.
Line17: Change the name of the pivot table.

If you still have a problem using this code, please write me in the comment box. Now, let me show you how this code works so that you can easily modify it as per your need.

Set Data_Sheet = ThisWorkbook.Worksheets("PivotTableData3")
Set Pivot_Sheet = ThisWorkbook.Worksheets("Pivot3")

In the above part of the code, we have specified the variables for the pivot table and source data worksheet. You can change the name of the worksheet from here.

PivotName = "PivotTable2"

In the above part of the code, enter the name of the pivot table on which you want to use this code.

Data_Sheet.Activate
Set StartPoint = Data_Sheet.Range("A1")
LastCol = StartPoint.End(xlToRight).Column
DownCell = StartPoint.End(xlDown).Row
Set DataRange = Data_Sheet.Range(StartPoint, Cells(DownCell, LastCol))

The above part of the code will create a dynamic range by using cell A1 from the data source worksheet.

It will check the last column and last row with data to create a dynamic range. Every time you run this macro it will create a new dynamic range.

'Ensure Pivot Table is Refreshed
 Pivot_Sheet.PivotTables(PivotName).RefreshTable
 
'Complete Message
 Pivot_Sheet.Activate
 MsgBox "Your Pivot Table is now updated."

The above part of the code will refresh the pivot table and show a message to the user that the pivot table is updated now.

Get the Excel File

Download

67 thoughts on “Dynamic Pivot Table Range that Don’t Needs to Update”

Andrew Simmons

9 Jul at

This code is giving me a run-time error. it says subscript out of range.

Range(“A6”).Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
“PC IT!R6C1:R1575C15″, Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:=”Sheet1!R3C1″, TableName:=”PivotTable1”, DefaultVersion _
:=xlPivotTableVersion10
Sheets(“Sheet1”).Select
Cells(3, 1).Select

Dim Data_Sheet As Worksheet
Dim Pivot_Sheet As Worksheet
Dim StartPoint As Range
Dim DataRange As Range
Dim PivotName As String
Dim NewRange As String
Dim LastCol As Long
Dim lastRow As Long
Sheets(“PC IT”).Select
‘Set Pivot Table & Source Worksheet

Set Data_Sheet = ThisWorkbook.Worksheets(“PC IT”)
Set Pivot_Sheet = ThisWorkbook.Worksheets(“sheet1”)

‘Enter in Pivot Table Name
PivotName = “PivotTable1”
‘Defining Staring Point & Dynamic Range
Data_Sheet.Activate
Set StartPoint = Data_Sheet.Range(“A1”)
LastCol = StartPoint.End(xlToRight).Column
DownCell = StartPoint.End(xlDown).Row
Set DataRange = Data_Sheet.Range(StartPoint, Cells(DownCell, LastCol))
NewRange = Data_Sheet.Name & “!” & DataRange.Address(ReferenceStyle:=xlR1C1)

‘Change Pivot Table Data Source Range Address
Pivot_Sheet.PivotTables(PivotName). _
ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:=NewRange)

‘Ensure Pivot Table is Refreshed
Pivot_Sheet.PivotTables(PivotName).RefreshTable

‘Complete Message
Pivot_Sheet.Activate
MsgBox “Your Pivot Table is now updated.”
Reply
Pradeep Kumar

29 Jun at

I like the table method but already Iam using the same. Thanks for the showing other options too.
Reply
- Puneet
  
  29 Jun at
  
  That’s great, Pradeep.
  Reply
Rodislav

15 May at

Hello!

I’m trying to implement your VBA code into my workbook but there is a problem.
After changing the names of the object in line 13,14 and 17 with

‘Set Pivot Table & Source Worksheet
Set Data_Sheet = ThisWorkbook.Worksheets(“SourceTab4”)
Set Pivot_Sheet = ThisWorkbook.Worksheets(“Pivot4”)

‘Enter in Pivot Table Name
PivotName = “Something”

when I launch the macro, there is an error window that pop up:

Method ‘PivotTables’ of object’_Worksheet’ failed:

And in the debugger this is selected in yellow:
Pivot_Sheet.PivotTables(PivotName). _
ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:=NewRange)

I don’t know if the problem comes from the Pivot Table Name or not because I gave nowhere a name of the pivot table, so I put a random name “Something” into the parenthesis.

The names of my sheets are : SourceTab4 and Pivot4

Thanks in advance!
Reply
srinivasan

7 Apr at

hi bro iam srinivasan iam an commerce student …. i have no knowlege on about vba code in excel kindly provide me some guidance on learning vba in excel…
my email id srinivasan.ragu1998@gmail.com
Reply
Willard Stevenson

6 Apr at

I have a pivot table based on a table which has dates in Col B (which is imported by Query from a file). When I insert a slicer choosing the date field it shows dates way beyond my data. Although the dates are not highlighted because my data doesn’t go that far, it bothers me that the scroll bar is active for unused dates. How can I fix this?
Reply
- Puneet
  
  6 Apr at
  
  Check out the format of dates you have in the ColB.
  Reply
- Sven
  
  8 Apr at
  
  Hi Willard,
  
  Try setting your retained data in the Pivottable to ‘None’:
  
  In your PivotTable Options, click the Data tab and then select ‘None’ from the Number of items to retain per field drop-down list.
  After this refresh your PivotTable.
  Reply
Gayathri

4 Apr at

Hi, I have multiple pivots in my workbook. Not all of it use the same data source. However, most of them use the same data source. Can a macro code me written to refresh all these pivots using the same data source? (Note: The source data also keeps changing (increasing / decreasing no. of rows) that should also be factored in while writing the code
Reply
Sayan Chakraborty

28 Mar at

Hi,
I am trying via vbscript :

‘———————————————————————————–
‘Set Pivot Table & Source Worksheet
‘———————————————————————————–
Set objExcel = CreateObject(“Excel.Application”)
Set Data_Sheet = objExcel.Workbooks.Open(“D:\Users\703220609\Documents\rtest\test\test_pvt.xlsx”)
Set Pivot_Sheet = Data_Sheet.Worksheets(“Sheet2”)

‘———————————————————————————–
‘Enter in Pivot Table Name
‘———————————————————————————–
PivotName = “PivotTable1”

‘———————————————————————————–
‘Defining Staring Point & Dynamic Range
‘———————————————————————————–
Data_Sheet.Activate
Set StartPoint = Data_Sheet.Range(“A1”)
LastCol = StartPoint.End(xlToRight).Column
DownCell = StartPoint.End(xlDown).Row
Set DataRange = Data_Sheet.Range(StartPoint, Cells(DownCell, LastCol))
NewRange = Data_Sheet.Name & “!” & DataRange.Address(ReferenceStyle:=xlR1C1)

‘———————————————————————————–
‘Change Pivot Table Data Source Range Address
‘———————————————————————————–
Pivot_Sheet.PivotTables(PivotName). _
ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:=NewRange)

‘———————————————————————————–
‘Ensure Pivot Table is Refreshed
‘———————————————————————————–
Pivot_Sheet.PivotTables(PivotName).RefreshTable

‘———————————————————————————–
‘Complete Message
‘———————————————————————————–

Pivot_Sheet.Activate
MsgBox “Your Pivot Table is now updated.”

I am calling the VBscript via R.
The error I am getting :
D:\Users\703220609\Documents\R_Code\script.vbs(21, 69) Microsoft VBScript compilation error: Expected ‘)’

Please help
Reply
KW

25 Mar at

Thank you, this works nicely.
Reply
Richard

20 Mar at

The VBA code worked perfect. This helps soooo much!!!! Thanks for the code.
Reply
Lakhvear Singh

1 Mar at

The VBA worked great for my pivot table. Many thanks!!!
Reply
Haakon

26 Feb at

In the beginning of your code, you wrote:
Dim lastRow As Long

But what is it used for? I can only see it once in the code, and that’s where you named it.
Reply
cat

18 Dec at

my source data is constantly being deleted / rewritten. so I still haven’t found one of these to work. The second one, SourceData… the offset formula is destroyed by the data-import/delete-empty-rows function i have (it pulls 50+ worksheets from external sources, pastes them in to 50+ sheets then concatenates those 50+ sheets into one master sheet… then removes blank rows). so my source data is constantly changing drastically and none of these methods is working for me yet
Reply
- peter
  
  28 Jan at
  
  use whole columns as source, not just filled rows
  Reply
Filipe

1 Dec at

Looked for a lot of solutions for refreshing the data source for pivots.
A lot of complicated solutions… This one, really simple, works perfectly! Thank you a lot!!
Reply
- Puneet
  
  15 Dec at
  
  I’m so glad you liked it. 🙂
  Reply
riaz

24 Sep at

I used option 1B but if I copy paste smaller range of data in the sheet, it still keeps the old data. If I clear out the data before copying it, it still keep the Table1 as large as old one. Which means I get “blanks” in pivot table.
Reply
- Puneet
  
  5 Nov at
  
  Thanks
  Reply
Christopher Massey

12 Dec at

I’m getting a Runtime Error 1004 When I get to the Set DataRange Line under ‘Define Start Point & Dynamic Range of Data….Any suggestions?
Reply
Plastic Cup

9 Oct at

Don’t use OFFSET! It is volatile, use INDEX instead:
=$A$2:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1))
$A$2 is the upper, left corner of your range.
You can play around with the INDEX range and the two COUNTA ranges to adjust to your needs.
Reply
Jonathan Koe

17 Mar at

Thanks for sharing this. However, I get a syntax error message when trying to run and it points to this particular line.

NewRange = Data_Sheet.Name & “!” & DataRange.Address(ReferenceStyle:=xlR1C1)

Any idea why/how to fix?
Reply
Anindya

28 Feb at

The Table approach works perfectly when we do this manually.. but say we have a template excel file and we have one data sheet and one pivot sheet.. and each time we delete all the rows (except the header one) from the template excel file and insert rows to this data sheet programmatically (with apache POI).. The table approach is NOT working when the number of rows for current iteration is greater than the number of rows in the template file..
Reply
- riaz
  
  24 Sep at
  
  I have the same issue. Did anyone reply directly to you?
  Reply
- peter
  
  28 Jan at
  
  use whole columns as source, not just filled rows
  Reply
Munoz Cassie

13 Jan at

When I run the code I get a Run-time error ‘1004’: Method “PivotTables” of object’_Worksheet’ failed. Upon debugging it is pointing the code in the Change Pivot Table Data Source Range Address. Any suggestions?
Reply
- Puneet
  
  16 Feb at
  
  Refer this:
  
  Line13: Change the name of source worksheet.
  Line14: Change the name of pivot table sheet.
  Line17: Change the name of the pivot table.
  Reply
Wilmar Meyer

22 Nov at

Thank you for the above code. It works 100%. But how can I change the above code to only use the actual UsedRange? As I have “” and hidden formulas lower down that I don’t want part of the UsedRange. I don’t want to use xlDown but rather the actual UsedRange….Any Suggestions please? 🙂
Reply
- Puneet
  
  16 Feb at
  
  best thing is to create a table (well, a table is a solution in itself) then use that table name in the source name.
  Reply
  - dev
    
    20 Dec at
    
    Hi Puneet, I am facing Invlaid procedure call or argument error
    Reply

Dynamic Pivot Table Range that Don’t Needs to Update

Apply Table to have an Auto-Updating Pivot Table Range

Convert Data into a Table Before Creating a Pivot Table

Convert Data into a Table After Creating a Pivot Table

Create a Dynamic Pivot Table Range with OFFSET Function

How does this Formula Work?

Update the Pivot Table using a VBA Code

Get the Excel File

More Pivot Table Tutorials

67 thoughts on “Dynamic Pivot Table Range that Don’t Needs to Update”

Leave a Comment Cancel reply