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

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

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

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

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

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