Update pivot cache with range variable

  • 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!