Pivot Table Clear All Filters

[MichaelR]

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

 

[Jim Thomlinson]

Define Clear all filters. Do you want to remove them or do you want to set
the values to All...

 

[MichaelR]

Set the values on all of the fields to "All." Sorry for not being clear.

 

[Debra Dalgleish]

You can use the ClearAllFilters method:

ActiveSheet.PivotTables(1).ClearAllFilters

 

[MichaelR]

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]

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]

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]

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]

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