98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Cache Index
Cache Index
Resolved · Medium Priority · Version 2003
Mon 9 Feb 2009, 12:19Delegate David said...
David has attended:
Excel VBA Intro Intermediate course
Excel Advanced course
Cache Index
Hi there
I wish to set up a subroutine that resets the cache index each time in a loop. I have a workbook with several worksheets. In each worksheet there is a different pivot table (yet with the same dimensions for every one). I want to assign the pivot cache index of one particular worksheet to the pivot tables in the rest of the worksheets:
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
The problem is that the line containing pt.CacheIndex does not work. The right hand side of the statement works okay but when assigned to the left, it produces an error. Any ideas?
Thanks very much for your help.
David
Fri 17 Apr 2009, 11:39Trainer Stephen said...
RE: Cache Index
Hi David
Thank you for your question
First let me apologise for the delay in responding to your post
I have copied the code into a workbook and it seems to run without error. This suggests that the error lies in the actual workbook
if this is still an issue for you I suggest you email the workbook to me so I can investigate further
my email address is stephen@stl-training.co.uk
Regards
Stephen
Thu 14 May 2009, 10:18Trainer Rich said...
RE: Cache Index
Hi David,
How are you?
Did you manage to fix this problem, or get a copy of your workbook over to Stephen for him to have a look at?
This question has now been left unattended for a while.
We like to keep down the large number of active forum posts we receive.
As we are waiting for your response for further information to help resolve your issue, we will be automatically marking this question as resolved in the next 5 days (unless you post a follow-up within that time).
We look forward to hearing from you.
Thank you.
Regards, Rich
Wed 20 May 2009: Automatically marked as resolved.
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:COUNT function vs COUNTA functionThe COUNTA function works in the same way as the COUNT function, except that it will count cells that contain text (labels) and also cells that contain numbers (values). The COUNT function will only count cells that contain numbers. Blank cells are not counted by either the COUNT or the COUNTA function. |
