Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Page Field in a Pivot Table and Print Chart + Data
Hi I have a chart dependant upon a pivot table.
There is a page field which enables selection of Fuel Type. There are three fuel types "Gas", "Electric" and "(All)". I want to run a macro which selects a fuel type, prints the chart and data and then selects the the next Fuel. I recorded a simple macro to do it once and then tried to hack it to repeat for the other fuels - the code is given below. Sub PrintAnnual() ' ' Macro to print the Charts and Data for Annual Mag Card Holders ' ' Print Details for Electricity ActiveSheet.PivotTables("PivotTable2").PivotFields ("FUEL").CurrentPage = "Electric" Sheets(Array("Annual Chart", "Annual Summary")).Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True ' Print Details for Gas ActiveSheet.PivotTables("PivotTable2").PivotFields ("FUEL").CurrentPage = "Gas" Sheets(Array("Annual Chart", "Annual Summary")).Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True ' Print Details for (All) ActiveSheet.PivotTables("PivotTable2").PivotFields ("FUEL").CurrentPage = "(All)" Sheets(Array("Annual Chart", "Annual Summary")).Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True ' Return to Contents Sheet Sheets("Workbook Contents").Select End Sub When I tested it I received the following error report. Run time error 1004. "Unable to get the pivot tables property of the worksheet class" I would appreciate it if someone could identify how to correct the code so that it works properly. Also is there a more elegant way of doing this? Regards |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Page Field in a Pivot Table and Print Chart + Data
My first question is:
Is the sheet that contains the pivot table the active sheet when the macro is run? The code is referencing ActiveSheet.PivotTables. You may try to reference it directly like Sheets("MyPivotTableSheet").PivotTables("PivotTabl e2").PivotFields("FUEL").CurrentPage = "Electric" to eliminate the possibility of another sheet being active at the time the code is run First step is to get the code to run then we can try to simplify the code best luck David where "MyPivotTableSheet" is the name of your worksheet "Philip J Smith" wrote: Hi I have a chart dependant upon a pivot table. There is a page field which enables selection of Fuel Type. There are three fuel types "Gas", "Electric" and "(All)". I want to run a macro which selects a fuel type, prints the chart and data and then selects the the next Fuel. I recorded a simple macro to do it once and then tried to hack it to repeat for the other fuels - the code is given below. Sub PrintAnnual() ' ' Macro to print the Charts and Data for Annual Mag Card Holders ' ' Print Details for Electricity ActiveSheet.PivotTables("PivotTable2").PivotFields ("FUEL").CurrentPage = "Electric" Sheets(Array("Annual Chart", "Annual Summary")).Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True ' Print Details for Gas ActiveSheet.PivotTables("PivotTable2").PivotFields ("FUEL").CurrentPage = "Gas" Sheets(Array("Annual Chart", "Annual Summary")).Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True ' Print Details for (All) ActiveSheet.PivotTables("PivotTable2").PivotFields ("FUEL").CurrentPage = "(All)" Sheets(Array("Annual Chart", "Annual Summary")).Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True ' Return to Contents Sheet Sheets("Workbook Contents").Select End Sub When I tested it I received the following error report. Run time error 1004. "Unable to get the pivot tables property of the worksheet class" I would appreciate it if someone could identify how to correct the code so that it works properly. Also is there a more elegant way of doing this? Regards |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Page Field in a Pivot Table and Print Chart + Data
Thanks for this, the macro now runs. I was running the macro from another
sheet and this was causing the problem. Sorry for the delay in responding but I was diverted to another problem. Regards Phil "dkinn" wrote: My first question is: Is the sheet that contains the pivot table the active sheet when the macro is run? The code is referencing ActiveSheet.PivotTables. You may try to reference it directly like Sheets("MyPivotTableSheet").PivotTables("PivotTabl e2").PivotFields("FUEL").CurrentPage = "Electric" to eliminate the possibility of another sheet being active at the time the code is run First step is to get the code to run then we can try to simplify the code best luck David where "MyPivotTableSheet" is the name of your worksheet "Philip J Smith" wrote: Hi I have a chart dependant upon a pivot table. There is a page field which enables selection of Fuel Type. There are three fuel types "Gas", "Electric" and "(All)". I want to run a macro which selects a fuel type, prints the chart and data and then selects the the next Fuel. I recorded a simple macro to do it once and then tried to hack it to repeat for the other fuels - the code is given below. Sub PrintAnnual() ' ' Macro to print the Charts and Data for Annual Mag Card Holders ' ' Print Details for Electricity ActiveSheet.PivotTables("PivotTable2").PivotFields ("FUEL").CurrentPage = "Electric" Sheets(Array("Annual Chart", "Annual Summary")).Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True ' Print Details for Gas ActiveSheet.PivotTables("PivotTable2").PivotFields ("FUEL").CurrentPage = "Gas" Sheets(Array("Annual Chart", "Annual Summary")).Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True ' Print Details for (All) ActiveSheet.PivotTables("PivotTable2").PivotFields ("FUEL").CurrentPage = "(All)" Sheets(Array("Annual Chart", "Annual Summary")).Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True ' Return to Contents Sheet Sheets("Workbook Contents").Select End Sub When I tested it I received the following error report. Run time error 1004. "Unable to get the pivot tables property of the worksheet class" I would appreciate it if someone could identify how to correct the code so that it works properly. Also is there a more elegant way of doing this? Regards |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change the name of the Data field in a Pivot Table | Excel Discussion (Misc queries) | |||
Sorting Page Area Field Data in Excel 2007 Pivot Table | Excel Discussion (Misc queries) | |||
Summin Pivot Table data from a date selected in the Page field | Excel Discussion (Misc queries) | |||
Pivot Table, Line Chart with Secondary Y axis, Page Field selection destroys Y2 axis | Charts and Charting in Excel | |||
Pivot Table Page Field | Excel Discussion (Misc queries) |