Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
change/set pivot table autofilter criteria with macro button
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("Se rvice") .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("Se rvice") .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("Se rvice") .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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
change/set pivot table autofilter criteria with macro button
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. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "jackie" wrote: 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("Se rvice") .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("Se rvice") .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("Se rvice") .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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
change/set pivot table autofilter criteria with macro button
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. "Shane Devenshire" wrote: 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. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "jackie" wrote: 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("Se rvice") .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("Se rvice") .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("Se rvice") .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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
change/set pivot table autofilter criteria with macro button
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autofilter with pivot table | Excel Worksheet Functions | |||
I tried to get around the problem of the pivot table field settingdefaulting to Count instead of Sum by running a macro of change the settingfrom Count to Sum. However, when I tried to run the Macro, I got error messageof run time error 1004, unable | Excel Worksheet Functions | |||
I tried to get around the problem of the pivot table field settingdefaulting to Count instead of Sum by running a macro of change the settingfrom Count to Sum. However, when I tried to run the Macro, I got error messageof run time error 1004, unable | Excel Discussion (Misc queries) | |||
Change pivot table source data via macro | Excel Discussion (Misc queries) | |||
Macro to change a Pivot Table | Excel Discussion (Misc queries) |