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