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.