Cell text as pivottable filter

[JayJay6]

Hi People,

I hope you can help me with a little problem.

I have a script (please see below) which uses cell text to filter a pivottable ("Pivottable2") on two pivotfields. The two pivotfields ("Region","Name") are filtered by the named cells ("RegionFilterRange","RegionFilterRange2").

If I filter the pivottable in one of the following two ways, it works fine:
1) enter a text in "RegionFilterRange"
OR
1) enter a text in "RegionFilterRange"
2) enter a text in "RegionFilterRange2"

BUT,

If I do it backwards:
1) enter a text in "RegionFilterRange2"
OR
1) enter a text in "RegionFilterRange2"
2) enter a text in "RegionFilterRange"

Nothing happens ?

What I am aiming at, is to enter a text in one cell or/and the other, not in a particular order ?

Also, If I clear the "RegionFilterRange"-cell, the pivottable clears the filter,
BUT if I clear the "RegionFilterRange2"-cell, nothing happens ?

any ideas ? Please advice.

Br,
J

P.S. I have attached a spreadsheet with an example.

[vba]
Public Sub UpdatePivotFieldFromRange( _
ByVal RangeName As String, _
ByVal FieldName As String, _
ByVal PivotTableName As String)

Dim Sheet As Worksheet
Dim pt As PivotTable
Dim rng1, rng2 As Range
Dim vecItems As Variant

Set rng1 = Application.Range("RegionFilterRange")
Set rng2 = Application.Range("RegionFilterRange2")

For Each Sheet In Application.ActiveWorkbook.Worksheets
On Error Resume Next
Set pt = Sheet.PivotTables("PivotTable2")
Next

On Error GoTo Ex

If Not pt Is Nothing Then

pt.ManualUpdate = True
Application.EnableEvents = False
Application.ScreenUpdating = False

Dim Field As PivotField, Field2 As PivotField

Set Field = pt.PivotFields("Region")
Set Field2 = pt.PivotFields("Name")

Field.ClearAllFilters
Field.EnableItemSelection = False

If Range(rng1, rng2).Text = "(All)" Then
Call ResetAllItems(pt, Range("Region", "Name"))

Else

vecItems2 = GetItems(Worksheets("Sheet1").Range("B2:B20"), rng2.Text)
Call SelectPivotItem(Field2, vecItems2)
vecItems1 = GetItems(Worksheets("Sheet1").Range("A2:A20"), rng1.Text)
Call SelectPivotItem(Field, vecItems1)



End If

pt.RefreshTable

End If


Ex:
pt.ManualUpdate = False
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
[/vba]

[JayJay6]

--