run-time error '9': Subscript out of range
 

Can anyone tell me why i get this error?

Sub ChangePivotCache()
'change pivot cache for all pivot tables in workbook
Dim pt As PivotTable
Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
For Each pt In wks.PivotTables
pt.CacheIndex = Sheets("Pivot").PivotTables(1).CacheIndex
Next pt
Next wks

End Sub

PLEASE HELP!!


--
jerredjohnson
------------------------------------------------------------------------
jerredjohnson's Profile: http://www.excelforum.com/member.php...o&userid=32236
View this thread: http://www.excelforum.com/showthread...hreadid=520228

run-time error '9': Subscript out of range
 

What line is giving you the error? Most likely it is
pt.CacheIndex = Sheets("Pivot").PivotTables(1).CacheIndex

Are you sure you have a sheet call "Pivot" and are you sure that
there is at least one pivot table on it?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"jerredjohnson"

wrote in message
news:jerredjohnson.24d6xn_1141844102.6615@excelfor um-nospam.com...

Can anyone tell me why i get this error?

Sub ChangePivotCache()
'change pivot cache for all pivot tables in workbook
Dim pt As PivotTable
Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
For Each pt In wks.PivotTables
pt.CacheIndex = Sheets("Pivot").PivotTables(1).CacheIndex
Next pt
Next wks

End Sub

PLEASE HELP!!


--
jerredjohnson
------------------------------------------------------------------------
jerredjohnson's Profile:
http://www.excelforum.com/member.php...o&userid=32236
View this thread:
http://www.excelforum.com/showthread...hreadid=520228