P
Philip J Smith
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
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