vba loop through pivots

[spinkung]

Hi All,

I'm trying to loop through some pivots in excel from access vba.

the top refresh of my code works (if i hardcode in the pivot name)
the second refresh gives an error (passing the pivot name into a variable)

Dim pvt As PivotTable
For Each pvt In xlSht2.PivotTables
xlSht2.PivotTables("PivotTable1").PivotCache.Refresh
xlSht2.PivotTables(pvt).PivotCache.Refresh
Next

the error:
unable to get the pivot tables property of the worksheet class

Anyone??

Thanks,
Spin

 

[chergh]

Try:

Dim pvt As PivotTable
For Each pvt In xlSht2.PivotTables
xlSht2.PivotTables("PivotTable1").PivotCache.Refresh
xlSht2.PivotTables(pvt.name).PivotCache.Refresh
Next

Or:

Dim pvt As PivotTable
For Each pvt In xlSht2.PivotTables
   pvt.PivotCache.Refresh
Next

 

[spinkung]

thanks chergh, second way worked a treat.