Update pivot cache with range variable

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • Hi all,

    I'm trying to update all pivot table with a macro. It has worked in the past for me really well but involved some manual intervention - i.e. choosing the initial range/pivot cache, then updating all other pivot tables using that range/pivot cache. What I'm trying to do now is take that part away from the end user by using a variable that is determined in other parts of the code. Can anyone spot what I'm doing wrong with this piece of code:

    Obviously this only forms part of the overall code. pt declared as PivotTable and wks as a Worksheet.

    Set ptcache = Sheets("Data").Range("A1").CurrentRegion
    For Each wks In ActiveWorkbook.Worksheets
        For Each pt In wks.PivotTables
            pt.CacheIndex = ptcache
        Next pt
    Next wks
  • Re: Update pivot cache with range variable

    OK, thought I might update this to reflect the solution I've been using in case anyone else wanted to know in the future.

    Basically I've converted the data range for my pivotcache to a table and, well, it automatically updates the pivotcache for any additional rows as far as I can see. I haven't encountered any problems with it so far anyway.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!