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("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
>
>
>
> "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
> >
> >
|
Similar Threads
