Macro Failure

[Paula]

Hi, I am not great with macros and can only get by with recording, not
actually writing VBA. I am recording a really simple macro which goes through
3 pages and changes the pivot chart Country to Belgium for example, then I
need to go to a hidden sheet, unhide the sheet and refresh the pivot on that
sheet, and hide the sheet again before I go back to the front page. Unhiding
and hiding the sheet seems to fail the macro as I get a debug. Any ideas how
to record a macro which has to refresh a pivot with a hidden sheet?


'
Sheets("SUMMARY").Select
ActiveSheet.PivotTables("PivotTable3").PivotFields("Client ID Name"). _
CurrentPage = "XXX-BELGIUM "
Sheets("ADV BOOKIND DAYS").Select
ActiveSheet.PivotTables("PivotTable4").PivotFields("Client ID Name"). _
CurrentPage = "XXX-BELGIUM "
Sheets("By Class").Select
ActiveSheet.PivotTables("PivotTable4").PivotFields("Client ID Name"). _
CurrentPage = "XXX-BELGIUM "
Sheets("FORMAT RAW").Visible = True
ActiveSheet.PivotTables("PivotTable2").PivotFields("Client ID Name"). _
CurrentPage = "XXX-BELGIUM "
Sheets("RAW DATA").Visible = True
Sheets("Sheet1").Select

It seems to stick at this line - 3rd up from bottom...
ActiveSheet.PivotTables("PivotTable2").PivotFields("Client ID Name"). _
CurrentPage = "XXX-BELGIUM

Any advice would be fantastic ...

Many thanks
Paula

 

[Fred Smith]

In each statement, you are missing the closing quote for your CurrentPage.
Add a " to the end of BELGIUM. Do so in each case.

Regards,
Fred.

 

[Paula]

Hi Fred,

the speech marks are at the end of the line further over - I think the
screen is cutting off at the side but they are there, so that doesn't seem to
be the issue. Any other ideas for hide and unhide sheets during a macro?

Thanks again - appreciate your time.

 

[Shane Devenshire]

Hi,

FYI:

There is no need to unhide and hide the sheets, just change ActiveSheet to
Sheets("yoursheetname")

Example:

Sheets("Manual
Grouping").PivotTables("PivotTable1").PivotFields("City").PivotItems("Dallas").Visible = False

 

[Paula]

Hi, I'm really sorry - my macro knowledge is very basic (instead of visio
basic )

can you explain in my example what I am doing wrong, I need to unhide the
pivot in the sequence to refresh but then don't want it to be visiable
afterwards, is there something I am doing wrong in the recording sequence?

Many thanks

 

[joeu2004]

It seems to stick at this line - 3rd up from bottom...
ActiveSheet.PivotTables("PivotTable2").PivotFields("Client ID Name"). _
CurrentPage = "XXX-BELGIUM

I believe you can continue a line (the underscore "_") only where whitespace
is permitted. ("Whitespace" is tabs and spaces.). You are trying to
continue a line between an object (PivotFields) and its property
(CurrentPage). Instead, this must all one line, or you can continue the line
just before "=".

HTH.


----- original posting -----

 

[joeu2004]

Errata....

You are trying to continue a line between an object
(PivotFields) and its property (CurrentPage).

My bad! Apparently whitespace is permitted after the period between the
object and its property. Klunk!


----- original posting -----