Hi people,
I hope Yuo can help me with a irritating problem:
I am trying to make 2 text cells ("RegionFilterRange1", "RegionFilterRange2") work as free text filters on my "pivotable2".
But I can't seem to get it work. It works fine when I have one filter ("RegionFilterRange1"), but if I try to add the second one, I get the error message: "Compile error: ByRef argument type mismatch".
any ideas ?
(I attach a worksheet 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, rng3 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, Field2 As PivotField
Set Field = pt.PivotFields("Region")
Set Field2 = pt.PivotFields("Name")
Field.Range(Field, Field2).ClearAllFilters
Field.EnableItemSelection = False
If Range(rng1, rng2).Text = "(All)" Then
Call ResetAllItems(pt, FieldName)
Else
vecItems1 = GetItems(Worksheets("Sheet1").Range("A2:A20"), rng1.Text)
vecItems2 = GetItems(Worksheets("Sheet1").Range("B2:B20"), rng2.Text)
Call SelectPivotItem(Field, vecItems1)
Call SelectPivotItem(Field2, vecItems2)
End If
pt.RefreshTable
End If
Ex:
pt.ManualUpdate = False
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub[/vba] ----------
best regards,
Jakob