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

J

jerredjohnson

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

Chip Pearson

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"
<[email protected]>
wrote in message
news:[email protected]...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Code to Refresh inbound data sheets, then Refresh Pivot Tables 3
refresh multiple pivot tables in workbook 2
Please help! Cannot open pivotTable source file.... 2
macro skips a command 4
Efficiency in code 1
Run-time error 9 6
Pivot Table..getting a list of all, sourec odata and sheets locate 4
Modify Protection Macro to allow formatting? 3

Top