Home / Pivot Table / Dynamic Pivot Table Range that Don’t Needs to Update

Dynamic Pivot Table Range that Don’t Needs to Update

- Written by Puneet

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
More Pivot Table Tutorials

Updating a pivot table is a pain, isn’t it? If you use pivot tables in your work frequently I’m sure you can understand.

The point is every time when you add new data to the source sheet you need to update the source range for the pivot table before you refresh your pivot table.

Now just imagine if you add data to your source sheet every day you have to update the source range every day.

And every time changing pivot table range is a mess. Yes, that’s right, the more frequently you add data, the more you need to update the source range.

So the point is you need a method to update the source range automatically when you add new data.

NOTE: Pivot tables are one of the INTERMEDIATE EXCEL SKILLS.

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.

  1. Select any of the cells in your data.
  2. Use shortcut key Control + T or Go to → Insert Tab → Tables → Table.
  3. You will get a pop-up window with your current data range.
    add table to update pivot table range
  4. Click OK.
  5. Now to create a pivot table select any cell of your data. Go to → Design Tab → Tools → Summarize With Pivot Table.
    new pivot table to update pivot table range
  6. 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.

  1. Select any of the cells in your data source.
  2. Use shortcut key Control + T or Go to → Insert Tab → Tables → Table.
  3. You will get a pop-up window with your current data range.
  4. Click OK.
  5. Now, select any of cells from your pivot table and Go to → Analyze → Data → Change Data Source → Change Data Source (Drop Down Menu).
  6. 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.
    change source data to update pivot table range
  7. 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.

  1. Go to → Formulas Tab → Defined Names → Name Manager.
  2. Once you click on name manager you will get a pop-up window.
    use name manager to update pivot table range
  3. In your name manager window click on new to create a named range.
  4. 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))
  5. 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.

  1. Line13: Change the name of the source worksheet.
  2. Line14: Change the name of the pivot table sheet.
  3. 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

More Pivot Table Tutorials

⇠ Back to Pivot Table Tutorial

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

  1. This tutorial on pivot tables in Excel is very helpful and easy to follow. It provides step-by-step instructions on how to automatically update the source data range, making it an essential tool for anyone who wants to work efficiently with large amounts of data.

    Reply

  2. Hi there,

    If there is a pivot table built by a certain table range, it is common and easy to make changes in source data and reflect in pivot table by using Pivot table Tools>Analyze>Refresh but how can one reflect changes made in pivot table to source data?

    Reply

  4. This is a great tutorial! I’ve been trying to figure out how to do this for a while.

    Reply

  5. hi there,
    I had a difficulty in updating pivot chart and pivot table. What I want to do is when click Ctrl+Alt+F9, all my data will automatically update and the pivot charts and pivot table will change automatically. But my macros codes were wrong. Any suggestions ? Thank you

    Reply

  6. hello, i think I did all of that correct but getting error (vba run time error ‘1004’: method ‘PivotTables’ of object’_Worksheet’ Failed) below is my code
    ‘=============================================================================================================
    Dim Data_Sheet1 As Worksheet
    Dim Pivot_Sheet1 As Worksheet
    Dim StartPoint As Range
    Dim DataRange As Range
    Dim PivotName1 As String
    Dim NewRange As String
    Dim LastCol As Long

    ‘Set Pivot Table & Source Worksheet
    Set Data_Sheet1 = ThisWorkbook.Worksheets(“Szczegóły”)
    Set Pivot_Sheet1 = ThisWorkbook.Worksheets(“Dane”)

    ‘Enter in Pivot Table Name
    PivotName1 = “Tabela przestawna2”

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

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

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

    ‘Complete Message
    Pivot_Sheet1.Activate
    ‘=============================================================================================================

    please help 🙂

    Reply

  7. Hello there 🙂

    The error occurs at changing the pivot table data source range address (line28)

    I got an error saying that it couldn’t find the file and run-time error ‘5’:
    Invalid procedure call or argument

    Reply

  8. Hi
    In my scenario, Header starts from A3 and there are other details in A1 and A2.
    How do I change the code to get data source range to start from A3.

    I tried to use your code but I am getting application error in the last line of code:
    Dim StartPoint As Range
    Dim DataRange As Range
    Dim NewRange As String
    Dim LastCol As Long
    Dim lastRow As Long

    Set PSheet = Worksheets(“Pivot1Test”)
    Set DSheet = Worksheets(“PaymentReviewTemplate”)

    DSheet.Activate
    Set StartPoint = DSheet.Range(“A3”)
    LastCol = StartPoint.End(xlToRight).Column
    DownCell = StartPoint.End(xlDown).Row
    Set DataRange = DSheet.Range(StartPoint, Cells(DownCell, LastCol))
    NewRange = DSheet.Name & “!” & DataRange.Address(ReferenceStyle:=xlR1C1)

    Could you please help me?

    Reply

  9. Hello,

    Thanks for the code. This code is running well in my sheet. However when copied the same code with different sub function name . It throws me with an error. Run time error ‘5’.
    “Invalid Procedure call and argument. Control stucks here :-

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

    Reply

  10. When I tried through offset its showing “data source reference is not valid in pivot” Can you please help on this error..

    Reply

  11. Using your VBA code, is there a way to make the Data_Sheet = ThisWorkbook.Worksheets(sheet1!$B$2)) work such that sheet1!$B$2 is the name of the source sheet to use? I want to update the source for the pivot table based on text pulled from a drop down list of the data sheets available. All of the data columns are the same in these data sheets, but the number of rows will be different and the data is specific for each sheet (they are part numbers).

    Reply

  12. Is it possible to make this run on multiple worksheets in the same workbook?

    Reply

    • you need to use a loop through all the workbook in the workbook.

      Reply

  13. I am running Excel 2016.

    I get a Runtime Error 5 regarding the following code in my module:
    ‘Change Pivot Table Data Source Range Address
    Pivot_Sheet.PivotTables(PivotName). _
    ChangePivotCache ActiveWorkbook. _
    PivotCaches.Create(SourceType:=xlDatabase, SourceData:=NewRange)

    Any clues?

    Reply

  14. I need to update my employee training tracker with a monthly HR employee report. The HR report has unique employee ID’s I can use in both. I would like to be able to automatically update the report each month and identify new employees and employees no longer with the company.

    Reply

    • need more words from you. trying to do it through a pivot table.

      Reply

  15. Hi,

    The one problem I am having is that my entire data range is not being selected. This is because my data tab has a blank line in between every data line so the data really is only picking up the first two lines. How do I fix this?

    Reply

  16. My data source is a table that automatically updated through the paste link command. So source data file is automatically updated. No one is changing the data in the source data file.
    Therefore I cannot update my pivot table using any of these methods. Is there any way to refresh the pivot table without refreshing every time.
    Thanks.

    Reply

  17. Thanks a lot Puneet. However all the methods are really good and i personally prefer name range with offset, it doesn’t need any macro and very simple and easy to use.
    thanks a lot for sharing wonderful tricks to make excel easy.

    Reply

  18. Hi
    When i run this code, i got “Invalid Procedure Call or Arguments” Error Occur.

    Can anyone explain it.

    Thnx

    Reply

  19. There is another simple trick. If your column headings are starting from A1, that is no rows above to it then you can simple put range as A:E or A:Z whatever it is. Every time when you add data below the table the range is updated automatically because you have selected the entire column.

    Reply

    • That’s also great but it will un-necessarily show the blank rows and will also not work if below your data you have another data which you don’t want in your pivot.

      Reply

  20. Hi,
    when i am tried to run this code, i am getting run time 1004 at

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

    can you resolve it

    Reply

  21. Pivot_Sheet.PivotTables(PivotName). _
    ChangePivotCache ActiveWorkbook. _
    PivotCaches.Create(SourceType:=xlDatabase, SourceData:=NewRange)
    when I tried to run code for changing range of data source for pivot table.I am getting runtime error 1004 and above portion is highlighted with yellow color.

    Reply

  23. Hi Puneet,

    Good day. Thanks for your sharing. I noticed there will be a message pop up when I want to filter the pivot table after opened the file again. Why? How to avoid this without need refreshing the Pivot Table?

    “The PivotTable report was saved without underlying data. Use the Refresh Data command to update the report”.

    If the data source sheet is always the 2nd worksheet next to Pivot Table.

    Set Data_Sheet = ThisWorkbook.Worksheets(“PivotTableData3”)

    Any “way” to change this code more flexible without a need to specify the exact worksheet name? I tried but it never worked for me.

    In fact, this is not my original approach. I am trying hard to create vba code in another master workbook (A). In that master workbook (A), I have a list of table to list out filenames saved in specific path.
    Earlier on, I referenced to other websites and managed to create vba code to copy my “default” format of Pivot Table in master workbook (A) to multiple closed workbooks as mentioned above.

    Thus, I need a vba code to change the data source of all Pivot Tables that copied to closed workbooks. Data sheet is just next worksheet of Pivot Table in closed workbooks.
    The macro run successfully but never changed the data source. I believe my codes were not perfectly to instruct pivot table to use the worksheet next to it as data sheet.

    Therefore, I am looking for alternative solution to help me and my colleague to improve our productivity as we need to change data source for maybe 250 files within few hours.

    The codes you shared would help if there is a way not to specify the exact name of data sheet in vba code and it will not ask user to refresh pivot table every time when open the file to filter the pivot table.

    Sorry for my long message. I did my best to work for it, but I failed.

    Reply

    • Hi Puneet,

      Good day.
      Do you have any good sample of vba code to change the data source of a Pivot Table that being copied from 1 master file to multiple closed workbooks? The copied Pivot Table still linked to the original data source in old workbook (master file). My intention is to remove the old data source of copied Pivot in closed workbooks and let the Pivot Table (always 1st sheet) to get the new data source in closed workbooks (always 2nd sheet) through the macro set in master file. Really appreciate your guidance. Thanks.
      **Waiting your reply since last year Oct.

      Reply

        • Thanks Puneet. I tried again and this round it seems like help me a lot. Maybe I too panic half year ago. Thank you so much.

          But, I still have new obstacles for below and hope you can help on this. I started to learn vba by reading through the forum only. Forgive me if I asking some funny questions.

          (1) My original PivotTable was originally tick with PivotTable Options > Data to “Refresh data when opening the file”. Once I run through the coding, it seems like this option is being removed. “Underlying data…..” message was popped up again. Any way to solve it or retain my PivotTable Options after run through the macro?

          (2) Possible to mark specifc column as last column? Let say, the last column is AT but I would like the last column of data source stop at column AP. Purpose: Trying to hide some information from Pivot Table Field. These columns cannot delete from data source.

          LastCol = StartPoint.End(xlToRight).Column

          Used to be my question and now I found solution by tweaked the codes.
          Q: How to make “Set Data_Sheet = ThisWorkbook.Worksheets(“PivotTableData3″)” more flexible by not mentioning the name of the worksheet?
          I might have 200 files with different name of worksheet or different name of sheet name required to refresh Pivot data source, so it might not efficient if i need to modify 1 by 1. **My PivotTable Name and name of pivot table worksheet is never changed. I tweaked the codes and it seems like work for me now.

          My new codes. Slightly tweaked but basically followed yours.

          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
          Dim ws As Worksheet: Set ws = ActiveWorkbook.Worksheets(“PivotTable”) ”CC: Newly added
          Dim wsNext As Worksheet ”CC: Newly added
          Set wsNext = ws.Next ”CC: Newly added

          ”CC:Not using because every file will have different datasource name –>
          ”Set Data_Sheet = ThisWorkbook.Worksheets(“Surname,FirstName”)

          ”CC:Not using ThisWorkbook because I cannot copy the code into new file 1 by 1 –>
          ”Set Pivot_Sheet = ThisWorkbook.Worksheets(“PivotTable”)

          ‘Set Pivot Table & Source Worksheet
          Set Pivot_Sheet = ActiveWorkbook.Worksheets(“PivotTable”) ”CC:Tweaked to ActiveWorkbook so that I can run from other workbook.
          Set Data_Sheet = ws.Next

          ‘Enter in Pivot Table Name
          PivotName = “PTSample”

          ‘Defining Staring Point & Dynamic Range
          Data_Sheet.Activate
          Set StartPoint = Data_Sheet.Range(“A7”)
          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 “Task Complete! ” & PivotName & ” is updated now.”

          End Sub

          Reply

          • Hi Puneet,

            Yeah! I found the trick for #2. I just need to hide my data source sheet for last 4 columns (Col AQ to AT) before running through the macro to change data source. It will automatically exclude the last 4 columns and invisible from PivotTable Fields.

            But, I still looking for solution on #1. Any recommendation?

            Btw, is it possible to have Pivot Auto Refresh once Workbook is opened? But the vba coding is added into “Worksheet” instead of “ThisWorkbook” or “Module”?

            Many Thanks.

  25. Puneet, the VBA code works great, thanks! One Question, what changes should I make to the code so that a second or third pivot table that’s linked to the same data source is also updated/refreshed?

    Thanks in advance,
    Nick

    Reply

    • I am also looking for the same. Please help on this also.

      Reply

  27. 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

  28. I like the table method but already Iam using the same. Thanks for the showing other options too.

    Reply

  29. 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

  30. 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

  31. 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

    • Check out the format of dates you have in the ColB.

      Reply

    • 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

  32. 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

  33. 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

  35. The VBA code worked perfect. This helps soooo much!!!! Thanks for the code.

    Reply

  37. 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

  38. 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

  39. 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

  40. 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

  41. 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

  42. 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

  43. 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

  44. 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

  45. 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

    • 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

  46. 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

    • 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

      • Hi Puneet, I am facing Invlaid procedure call or argument error

        Reply

Leave a Comment