change/set pivot table autofilter criteria with macro button

[jackie]

Hi,
I have 4 filters on 3 pivot tables, A, F, N. M. I need a button that when
selected will automatically select one of the filters.
I tried to do it by setting the autofilters on the three charts to Show All.
I then recorded a macro for A, for example by select A from each pivot table.
I did this for all four filters. and added the macros to buttons. The first
one works, but when you hit any of the subsequent buttons, you get an error.
In the data below, the last line for N in Chart 38 is in yellow when
debugged.

ActiveWindow.Visible = False
Windows("Force Protection - Corporate.xls").Activate
ActiveSheet.ChartObjects("Chart 38").Activate
With ActiveChart.PivotLayout.PivotTable.PivotFields("Service")
.PivotItems("A").Visible = False
.PivotItems("M").Visible = False
.PivotItems("N").Visible = False
End With
ActiveWindow.Visible = False
Windows("Force Protection - Corporate.xls").Activate
ActiveSheet.ChartObjects("Chart 40").Activate
With ActiveChart.PivotLayout.PivotTable.PivotFields("Service")
.PivotItems("A").Visible = False
.PivotItems("M").Visible = False
.PivotItems("N").Visible = False
End With
ActiveWindow.Visible = False
Windows("Force Protection - Corporate.xls").Activate
ActiveSheet.ChartObjects("Chart 48").Activate
With ActiveChart.PivotLayout.PivotTable.PivotFields("Service")
.PivotItems("A").Visible = False
.PivotItems("M").Visible = False
.PivotItems("N").Visible = False
End With
End Sub

Can someone tell me what is wrong and how I can fix it. Thanks.

 

[Shane Devenshire]

Can't tell, but if the Worksheet is not visible you can't activate it. I
would remove all the
ActiveWindow.Visible = False
lines.

I don't understand the multiple button thing? You have showed us one macro
which works against 3 pivot tables, but what is the macro for the other
buttons? Or what is the macro you ran before this one, which worked, and now
this one fails.

 

[jackie]

I have 3 pivot charts in an excel spreadsheet. The 3 charts show Orders,
Backorders, and Inventory for North, South, East and West. So the auto filter
in the Orders chart can be filtered by these values of north, south, east and
west.
I put 4 buttons in the 4thquadron of the spreadsheet. I named them North,
South East and West. Then I selected Show All and recorded a Macro for North.
While recording I went to each of the thre charts and selected North. Then I
stopped the macro. and assigned it to the North button. I did this for each
of the buttons, each time returning the charts to Show all before recording.
When I tried to execute the macros, North worked the first time I used it.
It selected North in all of the auto filters in the three charts. So now all
of the charts have North selected. If I go to the next button/macro and
select South, it won't work because when I recorded the macro I was at Show
All. If I execute any of the other macros for East and West they won't work,
plus when I go back to the North button, which originally worked, it doesn't
work either.
I know it has to do with the position of the charts (what they are showing)
when I execute the macro. How do I create a macro that will select category
from the auto filter no matter what the chart is currently showing.

 

[Herbert Seidenberg]

Excel 2007
Page select macro.
The True statement has to come first:
..PivotItems("Xxxx").Visible = True
My example has charts on separate sheets.
No embedded charts (too difficult)
http://www.mediafire.com/file/zz3gm0wtjdz/03_15_09.xlsm