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

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"
<jerredjohnson.24d6xn_1141844102.6615@excelforum-nospam.com>
wrote in message
news:jerredjohnson.24d6xn_1141844102.6615@excelforum-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
>