|
Macro Failure
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 |
Macro Failure
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" wrote in message ... 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 |
Macro Failure
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. "Fred Smith" wrote: 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" wrote in message ... 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 |
Macro Failure
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 -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Paula" wrote: 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 |
Macro Failure
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 "Shane Devenshire" wrote: 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 -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Paula" wrote: 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 |
Macro Failure
"Paula" wrote:
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 ----- "Paula" wrote: 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 |
Macro Failure
Errata....
I wrote: 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 ----- " wrote: "Paula" wrote: 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 ----- "Paula" wrote: 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 |
| All times are GMT +1. The time now is 12:46 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com