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