Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Clear All Filters
Is is possible to write a macro that will clear all of the filters in a pivot
table/chart (i.e. the report filter, column labels and row labels filters) regardless of which pivot fields are being used in the report? Thanks, Michael |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Clear All Filters
Define Clear all filters. Do you want to remove them or do you want to set
the values to All... -- HTH... Jim Thomlinson "MichaelR" wrote: Is is possible to write a macro that will clear all of the filters in a pivot table/chart (i.e. the report filter, column labels and row labels filters) regardless of which pivot fields are being used in the report? Thanks, Michael |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Clear All Filters
Set the values on all of the fields to "All." Sorry for not being clear.
"Jim Thomlinson" wrote: Define Clear all filters. Do you want to remove them or do you want to set the values to All... -- HTH... Jim Thomlinson "MichaelR" wrote: Is is possible to write a macro that will clear all of the filters in a pivot table/chart (i.e. the report filter, column labels and row labels filters) regardless of which pivot fields are being used in the report? Thanks, Michael |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Clear All Filters
You can use the ClearAllFilters method:
ActiveSheet.PivotTables(1).ClearAllFilters MichaelR wrote: Set the values on all of the fields to "All." Sorry for not being clear. "Jim Thomlinson" wrote: Define Clear all filters. Do you want to remove them or do you want to set the values to All... -- HTH... Jim Thomlinson "MichaelR" wrote: Is is possible to write a macro that will clear all of the filters in a pivot table/chart (i.e. the report filter, column labels and row labels filters) regardless of which pivot fields are being used in the report? Thanks, Michael -- Debra Dalgleish Contextures www.contextures.com/tiptech.html Blog: http://blog.contextures.com |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Clear All Filters
Thank you, Debra! That was hugely helpful.
Could I also ask you another question which is the following: I have a sales database with sales by month for the last three years that is linked to a pivot chart in excel. I am trying to configure my pivot table to have a depth of two columns - current month and year-to-date. So, for example, if I put product class as the row field, I would like to see all of my products along the x-axis, each with two columns - one for current month and one for year to date. Is there any way to do this within the pivot charts? I tried to solve the problem by having two data items for sales $. One of the data items was summarized as "SUM" and the other as "Running Total in" month. This allowed me to calculate the Year-to-date sales at any given month but it didn't work when I only had one month filtered because the running total of sales $ became equal to the sum of sales $. I'm sorry for the long description - I didn't know how else to explain the problem. If you have any ideas, please help. Thanks again for all of your help so far. Michael "Debra Dalgleish" wrote: You can use the ClearAllFilters method: ActiveSheet.PivotTables(1).ClearAllFilters MichaelR wrote: Set the values on all of the fields to "All." Sorry for not being clear. "Jim Thomlinson" wrote: Define Clear all filters. Do you want to remove them or do you want to set the values to All... -- HTH... Jim Thomlinson "MichaelR" wrote: Is is possible to write a macro that will clear all of the filters in a pivot table/chart (i.e. the report filter, column labels and row labels filters) regardless of which pivot fields are being used in the report? Thanks, Michael -- Debra Dalgleish Contextures www.contextures.com/tiptech.html Blog: http://blog.contextures.com |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Clear All Filters
Could you add a CurrSales column to the source data? If you can, enter a
formula to check if the month is current. For example: =IF(MONTH(G2)=SelMonth,P2,0) SelMonth is a named cell where you've typed a month number. Then, add that field the to values area, along with the Sales $ field. MichaelR wrote: Thank you, Debra! That was hugely helpful. Could I also ask you another question which is the following: I have a sales database with sales by month for the last three years that is linked to a pivot chart in excel. I am trying to configure my pivot table to have a depth of two columns - current month and year-to-date. So, for example, if I put product class as the row field, I would like to see all of my products along the x-axis, each with two columns - one for current month and one for year to date. Is there any way to do this within the pivot charts? I tried to solve the problem by having two data items for sales $. One of the data items was summarized as "SUM" and the other as "Running Total in" month. This allowed me to calculate the Year-to-date sales at any given month but it didn't work when I only had one month filtered because the running total of sales $ became equal to the sum of sales $. I'm sorry for the long description - I didn't know how else to explain the problem. If you have any ideas, please help. Thanks again for all of your help so far. Michael "Debra Dalgleish" wrote: You can use the ClearAllFilters method: ActiveSheet.PivotTables(1).ClearAllFilters MichaelR wrote: Set the values on all of the fields to "All." Sorry for not being clear. "Jim Thomlinson" wrote: Define Clear all filters. Do you want to remove them or do you want to set the values to All... -- HTH... Jim Thomlinson "MichaelR" wrote: Is is possible to write a macro that will clear all of the filters in a pivot table/chart (i.e. the report filter, column labels and row labels filters) regardless of which pivot fields are being used in the report? Thanks, Michael -- Debra Dalgleish Contextures www.contextures.com/tiptech.html Blog: http://blog.contextures.com -- Debra Dalgleish Contextures www.contextures.com/tiptech.html Blog: http://blog.contextures.com |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Clear All Filters
Debra,
Thank you for your suggestion - it was very helpful! I also took a look at your website and I got a lot of other questions answered so thank you again. I was wondering if you could answer another question for me. I'm trying to write a macro that encompasses the one that I asked you about previously. I want to link the macro to a "Panic!" button so that the user can press it if he/she wants to start the pivot table from scratch. However, I don't want the macro to delete the pivot table and insert a new one. Instead, I was hoping that I could just remove all row fields, column fields, page fields and data items (regardless of how many or which ones they are). This way the user won't have to wait for the pivot table to import the external data everytime they hit the panic button. Is this macro possible and if so, do you have any ideas for how I could write it? Thank you again for all of your help! Michael "Debra Dalgleish" wrote: Could you add a CurrSales column to the source data? If you can, enter a formula to check if the month is current. For example: =IF(MONTH(G2)=SelMonth,P2,0) SelMonth is a named cell where you've typed a month number. Then, add that field the to values area, along with the Sales $ field. MichaelR wrote: Thank you, Debra! That was hugely helpful. Could I also ask you another question which is the following: I have a sales database with sales by month for the last three years that is linked to a pivot chart in excel. I am trying to configure my pivot table to have a depth of two columns - current month and year-to-date. So, for example, if I put product class as the row field, I would like to see all of my products along the x-axis, each with two columns - one for current month and one for year to date. Is there any way to do this within the pivot charts? I tried to solve the problem by having two data items for sales $. One of the data items was summarized as "SUM" and the other as "Running Total in" month. This allowed me to calculate the Year-to-date sales at any given month but it didn't work when I only had one month filtered because the running total of sales $ became equal to the sum of sales $. I'm sorry for the long description - I didn't know how else to explain the problem. If you have any ideas, please help. Thanks again for all of your help so far. Michael "Debra Dalgleish" wrote: You can use the ClearAllFilters method: ActiveSheet.PivotTables(1).ClearAllFilters MichaelR wrote: Set the values on all of the fields to "All." Sorry for not being clear. "Jim Thomlinson" wrote: Define Clear all filters. Do you want to remove them or do you want to set the values to All... -- HTH... Jim Thomlinson "MichaelR" wrote: Is is possible to write a macro that will clear all of the filters in a pivot table/chart (i.e. the report filter, column labels and row labels filters) regardless of which pivot fields are being used in the report? Thanks, Michael -- Debra Dalgleish Contextures www.contextures.com/tiptech.html Blog: http://blog.contextures.com -- Debra Dalgleish Contextures www.contextures.com/tiptech.html Blog: http://blog.contextures.com |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Clear All Filters
This macro will clear all the fields from the pivot table, and show the
pivot table field list when the active cell is inside the pivot table area: '==================== Sub ClearPivot() On Error Resume Next Dim pt As PivotTable Dim pf As PivotField Set pt = ActiveSheet.PivotTables(1) With pt For Each pf In .VisibleFields pf.Orientation = xlHidden Next pf End With ActiveWorkbook.ShowPivotTableFieldList = True End Sub '==================== MichaelR wrote: Debra, Thank you for your suggestion - it was very helpful! I also took a look at your website and I got a lot of other questions answered so thank you again. I was wondering if you could answer another question for me. I'm trying to write a macro that encompasses the one that I asked you about previously. I want to link the macro to a "Panic!" button so that the user can press it if he/she wants to start the pivot table from scratch. However, I don't want the macro to delete the pivot table and insert a new one. Instead, I was hoping that I could just remove all row fields, column fields, page fields and data items (regardless of how many or which ones they are). This way the user won't have to wait for the pivot table to import the external data everytime they hit the panic button. Is this macro possible and if so, do you have any ideas for how I could write it? Thank you again for all of your help! Michael "Debra Dalgleish" wrote: Could you add a CurrSales column to the source data? If you can, enter a formula to check if the month is current. For example: =IF(MONTH(G2)=SelMonth,P2,0) SelMonth is a named cell where you've typed a month number. Then, add that field the to values area, along with the Sales $ field. MichaelR wrote: Thank you, Debra! That was hugely helpful. Could I also ask you another question which is the following: I have a sales database with sales by month for the last three years that is linked to a pivot chart in excel. I am trying to configure my pivot table to have a depth of two columns - current month and year-to-date. So, for example, if I put product class as the row field, I would like to see all of my products along the x-axis, each with two columns - one for current month and one for year to date. Is there any way to do this within the pivot charts? I tried to solve the problem by having two data items for sales $. One of the data items was summarized as "SUM" and the other as "Running Total in" month. This allowed me to calculate the Year-to-date sales at any given month but it didn't work when I only had one month filtered because the running total of sales $ became equal to the sum of sales $. I'm sorry for the long description - I didn't know how else to explain the problem. If you have any ideas, please help. Thanks again for all of your help so far. Michael "Debra Dalgleish" wrote: You can use the ClearAllFilters method: ActiveSheet.PivotTables(1).ClearAllFilters MichaelR wrote: Set the values on all of the fields to "All." Sorry for not being clear. "Jim Thomlinson" wrote: Define Clear all filters. Do you want to remove them or do you want to set the values to All... -- HTH... Jim Thomlinson "MichaelR" wrote: Is is possible to write a macro that will clear all of the filters in a pivot table/chart (i.e. the report filter, column labels and row labels filters) regardless of which pivot fields are being used in the report? Thanks, Michael -- Debra Dalgleish Contextures www.contextures.com/tiptech.html Blog: http://blog.contextures.com -- Debra Dalgleish Contextures www.contextures.com/tiptech.html Blog: http://blog.contextures.com -- Debra Dalgleish Contextures www.contextures.com/tiptech.html Blog: http://blog.contextures.com |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Clear All Filters
Debra,
The clear all filters method that you wrote above (ActiveSheet.PivotTables(1).ClearAllFilters) doesn't work in MS Excel 2003. Is there any other way that I could do this in 2003? Thanks, Michael "Debra Dalgleish" wrote: This macro will clear all the fields from the pivot table, and show the pivot table field list when the active cell is inside the pivot table area: '==================== Sub ClearPivot() On Error Resume Next Dim pt As PivotTable Dim pf As PivotField Set pt = ActiveSheet.PivotTables(1) With pt For Each pf In .VisibleFields pf.Orientation = xlHidden Next pf End With ActiveWorkbook.ShowPivotTableFieldList = True End Sub '==================== MichaelR wrote: Debra, Thank you for your suggestion - it was very helpful! I also took a look at your website and I got a lot of other questions answered so thank you again. I was wondering if you could answer another question for me. I'm trying to write a macro that encompasses the one that I asked you about previously. I want to link the macro to a "Panic!" button so that the user can press it if he/she wants to start the pivot table from scratch. However, I don't want the macro to delete the pivot table and insert a new one. Instead, I was hoping that I could just remove all row fields, column fields, page fields and data items (regardless of how many or which ones they are). This way the user won't have to wait for the pivot table to import the external data everytime they hit the panic button. Is this macro possible and if so, do you have any ideas for how I could write it? Thank you again for all of your help! Michael "Debra Dalgleish" wrote: Could you add a CurrSales column to the source data? If you can, enter a formula to check if the month is current. For example: =IF(MONTH(G2)=SelMonth,P2,0) SelMonth is a named cell where you've typed a month number. Then, add that field the to values area, along with the Sales $ field. MichaelR wrote: Thank you, Debra! That was hugely helpful. Could I also ask you another question which is the following: I have a sales database with sales by month for the last three years that is linked to a pivot chart in excel. I am trying to configure my pivot table to have a depth of two columns - current month and year-to-date. So, for example, if I put product class as the row field, I would like to see all of my products along the x-axis, each with two columns - one for current month and one for year to date. Is there any way to do this within the pivot charts? I tried to solve the problem by having two data items for sales $. One of the data items was summarized as "SUM" and the other as "Running Total in" month. This allowed me to calculate the Year-to-date sales at any given month but it didn't work when I only had one month filtered because the running total of sales $ became equal to the sum of sales $. I'm sorry for the long description - I didn't know how else to explain the problem. If you have any ideas, please help. Thanks again for all of your help so far. Michael "Debra Dalgleish" wrote: You can use the ClearAllFilters method: ActiveSheet.PivotTables(1).ClearAllFilters MichaelR wrote: Set the values on all of the fields to "All." Sorry for not being clear. "Jim Thomlinson" wrote: Define Clear all filters. Do you want to remove them or do you want to set the values to All... -- HTH... Jim Thomlinson "MichaelR" wrote: Is is possible to write a macro that will clear all of the filters in a pivot table/chart (i.e. the report filter, column labels and row labels filters) regardless of which pivot fields are being used in the report? Thanks, Michael -- Debra Dalgleish Contextures www.contextures.com/tiptech.html Blog: http://blog.contextures.com -- Debra Dalgleish Contextures www.contextures.com/tiptech.html Blog: http://blog.contextures.com -- Debra Dalgleish Contextures www.contextures.com/tiptech.html Blog: http://blog.contextures.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hide Pivot Table Filters | Excel Discussion (Misc queries) | |||
Pivot Table-using filters or groups | Excel Discussion (Misc queries) | |||
How to clear pivot table catche | Excel Discussion (Misc queries) | |||
Pivot table filters | Excel Discussion (Misc queries) | |||
Pivot Table Value and Label Filters | Excel Discussion (Misc queries) |