Linking 3 auto filters to a pivot table on another worksheet

[daynereed]

Hi all,

Can anyone help?

I have used the code below to successfully link 3 auto filters to a
pivot table on another worksheet.

1. Area (Sales area)
2. MA (Market area)
3. Name (Centre name)

When I select values using each of the 3 filters it works perfectly
i.e. the pivot table changes to show the data from my selection. The
problem I have is that I want the filters to work independently e.g.
if I select only the area filter and the other filters remain un
selected, I want the pivot table to show all of the data for the
selected area only.
I assume I need to use an IF statement to say if a filter is not used
then show "(ALL)" default aut filter value?

The code I am using is as follows:

Private Sub Worksheet_Activate()
Dim rCell As Range, strArea As String
Dim strMA As String, StrName As String

On Error Resume Next
For Each rCell In Blad1.Range("A4:C4")
Select Case UCase(rCell)
Case "AREA"
strArea = rCell.End(xlDown)
Case "MA"
strMA = rCell.End(xlDown)
Case "NAME"
StrName = rCell.End(xlDown)

Case Else
End Select
Next rCell

With Me.PivotTables("PivotTable1")
.PivotFields("Area").CurrentPage = strArea
.PivotFields("MA").CurrentPage = strMA
.PivotFields("Name").CurrentPage = StrName

End With
On Error GoTo 0
End Sub

 

[ShaneDevenshire]

Hi,

How about setting the other AutoFilters to All. But regardless you will
need to set all three filters because Excel will never know when to use one
and when to use all three.

 

[daynereed]

Hi,

How about setting the other AutoFilters to All.  But regardless you will
need to set all three filters because Excel will never know when to use one
and when to use all three.

--
Thanks,
Shane Devenshire















- Show quoted text -

Thanks Shane. I guess the two formats are incompatible

Chris