Change Page Field in a Pivot Table and Print Chart + Data

[Guest]

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

 

[Guest]

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("PivotTable2").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

 

[Guest]

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