Macro to refresh hidden pivot table

[juex]

I have been struggling with a problem in Excel and hopefully someone can
help me. I manage an Excel-file which is used to manage or sales guys and their their efforts in the markets. I want to create a bulit in report into the file. A dynamic report which can be updated by the user. The user fills in customer names (one per line) and properties into the file. I have marked this area and made it into a pivot table. In a separate report sheet I have
a few graphs. The source data for these graphs can be found in the pivot report. I have sorted the pivot tables (I have three different report tables) so that the customer with the biggest potential is at the top. When the user fills in a new customer which has very much potential in USD and the pivot tables are refreshed the new customer will be shown at the top of the tables and subsequently the graphs in the report sheet will be updated and show the new customer. I have recorded a macro (using Excels recorder) that executes the refreshing of the Pivot tables.
I have added a push button to the report sheet, and the push button is linked to the macro. So, when the user pushes the button the graphs in the report sheet are updated. This works very well. The problem occurs when I want to hide the Pivot tables. Obviously I don't want to have visible pivot tables in the user version. The previously described macro does not work when the Pivot is hidden. I tried to record a new macro according to the following logic; First I hide the Pivot table sheet. Then I place the cursor in the report sheet. I then select record macro. The first thing that I do while recording is unhiding the Pivot
table sheet, then I place the cursor in the Pivot table and click the refresh
button in the toolbar. Then I hide the Pivot sheet and go back to the report sheet and stop recording. This macro does not work, the following message comes up; Run-time error '1004'; Unable to get the PivotTables property of the Worksheet class

When I press debug the following piece of VBA code is shown:

Sub TESTI2()
'
' TESTI2 Macro
' Macro recorded 19.3.2009 by jukka-pekka niemi'

'
Range("B21").Select
Sheets("Target pivot3level").Visible = True
Range("A6").Select
ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh
ActiveWindow.SelectedSheets.Visible = False
Range("B13").Select
End Sub

VBA enters a yellow arrow at the line
"ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh" inicating that the problem lies here?

The first and last commands are simply from moving the cursor to the report sheet.

Do you have any idea how I could fix this. I'm out of ideas. I have tried to
have three different Pivot tables in different sheets but that doesn't help.

I would very appreciate if you could help me out.

Best regards,

Juex

 

[dave3009]

Try just using

Sheets("Target pivot3level").PivotTables("PivotTable4").PivotCache.Refresh

 

[juex]

Thanks a lot Dave!

Using your tip + some other changes to the code my Excel file is now working properly. Like you implied, the ActiveSheet commands messed up the macro. By removing them from the refresh part and hide/unhide part I managed to get the commands working

Thanks alot

 

[dave3009]

Yeah

I think another important note is that it is not necessary to make visible any sheets when doing anything to them. Also you do not need to select any ranges if you have no intention of using them. The macro recorder is fantastic but like many code generators it records loads of unnecessary stuff

Anyways glad it helped

Dave