Run-time error 5: Invalid procedure call or argument in PivotTable filter VBA

[Gersie]

I'm trying to copy, create and rename a sheet named "ASPAC CAP" and update the selection pivot table field of the newly created and renamed sheet. The macro below successfully copied, created and renamed the new sheet. however I'm encountering a code error below when filtering the pivot table (PivotTable6) with Pivot field (Range 1.[Country].[Country]). I'm trying to filter the Pivot table using the value/values on "L1:L2" on the newly created sheet based on their "response".


I'm getting a run-time error 5: Invalid procedure call or argument on line 22:

Invalid procedure call or argument. Can anyone help me on this code?

Sub Marco1()
'
' Macro 1 Macro
'
Application.ScreenUpdating = False
Dim response as String
response = InputBox("Please enter Country initials")
Sheets("ASPAC CAP").Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = "ASPAC CAP - " & response
Application.ScreenUpdating = True

ActiveSheet.Range("J1").Value = response
ActiveSheet.Range("L1:L2").Copy
ActiveSheet.Range("L1:L2").PasteSpecial xlPasteValues
Application.CutCopyMode = False

FilterArray = Application.Transpose(ActiveSheet.Range("L1:L2").Value)
Dim myPF1 as PivotField
Set myPF1 = ActiveSheet.PivotTables("PivotTable6").PivotFields("[Range 1.[Country].[Country]")
MyPF1.ClearAllFilters
MyPF1.EnableMultipleItems = True

NumberOfElements = UBound(FilterArray) - LBound(FilterArray) +1

If NumberOfElements > 0 Then
    With myPF1
        For i = 1 to myPF1.PivotItems.Count
        j = 0
       Do While j < NumberOfElements
               If myPF1.PivotItems( i ).Name = FilterArray ( j ) Then
    myPF1.PivotItems(myPF1.PivotItems( i ).Name.Visible = True
       Exit Do
       Else
    myPF1.PivotItems(myPF1.PivotItems( i ).Name.Visible = False
               End If
               j = j + 1
       Loop
       Next i 
    End With
End If

'
End Sub

 

[Zot]

Not sure if it is just me but where is Line 22? ?

 

[Gersie]

Not sure if it is just me but where is Line 22? ?

Oh sorry, I thought I included the actual line in my post. the error is happening at this line

myPFI1.EnableMultipleItems = True

 

[RoryA]

The code as posted wouldn't run as it's missing a few parentheses.

 

[Gersie]

The code as posted wouldn't run as it's missing a few parentheses.

Sorry Rory,

Please see the corrected code below:
still having issues with line 22:

myPFI1.EnableMultipleItems = True

Sub Marco1()
'
' Macro 1 Macro
'
Application.ScreenUpdating = False
Dim response as String
response = InputBox("Please enter Country initials")
Sheets("ASPAC CAP").Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = "ASPAC CAP - " & response
Application.ScreenUpdating = True

ActiveSheet.Range("J1").Value = response
ActiveSheet.Range("L1:L2").Copy
ActiveSheet.Range("L1:L2").PasteSpecial xlPasteValues
Application.CutCopyMode = False

FilterArray = Application.Transpose(ActiveSheet.Range("L1:L2").Value)
Dim myPF1 as PivotField
Set myPF1 = ActiveSheet.PivotTables("PivotTable6").PivotFields("[Range 1.[Country].[Country]")
MyPF1.ClearAllFilters
MyPF1.EnableMultipleItems = True

NumberOfElements = UBound(FilterArray) - LBound(FilterArray) +1

If NumberOfElements > 0 Then
    With myPF1
        For i = 1 to myPF1.PivotItems.Count
        j = 0
       Do While j < NumberOfElements
               If myPF1.PivotItems( i ).Name = FilterArray ( j ) Then
    myPF1.PivotItems(myPF1.PivotItems( i ).Name).Visible = True
       Exit Do
       Else
    myPF1.PivotItems(myPF1.PivotItems( i ).Name).Visible = False
               End If
               j = j + 1
       Loop
       Next i 
    End With
End If

'
End Sub

 

[RoryA]

You should get a 438 error there since the property is EnableMultiplePageItems.

 

[Gersie]

You should get a 438 error there since the property is EnableMultiplePageItems.

Hi Rory,

Thank you for your patience, I pasted a wrong property. it's supposed to be EnableMultiplePageItems

the error is: Run-time error 5: Invalid procedure call or argument in PivotTable filter VBA

Sub Marco1()
'
' Macro 1 Macro
'
Application.ScreenUpdating = False
Dim response as String
response = InputBox("Please enter Country initials")
Sheets("ASPAC CAP").Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = "ASPAC CAP - " & response
Application.ScreenUpdating = True

ActiveSheet.Range("J1").Value = response
ActiveSheet.Range("L1:L2").Copy
ActiveSheet.Range("L1:L2").PasteSpecial xlPasteValues
Application.CutCopyMode = False

FilterArray = Application.Transpose(ActiveSheet.Range("L1:L2").Value)
Dim myPF1 as PivotField
Set myPF1 = ActiveSheet.PivotTables("PivotTable6").PivotFields("[Range 1.[Country].[Country]")
MyPF1.ClearAllFilters
MyPF1.EnableMultiplePageItems = True

NumberOfElements = UBound(FilterArray) - LBound(FilterArray) +1

If NumberOfElements > 0 Then
    With myPF1
        For i = 1 to myPF1.PivotItems.Count
        j = 0
       Do While j < NumberOfElements
               If myPF1.PivotItems( i ).Name = FilterArray ( j ) Then
    myPF1.PivotItems(myPF1.PivotItems( i ).Name).Visible = True
       Exit Do
       Else
    myPF1.PivotItems(myPF1.PivotItems( i ).Name).Visible = False
               End If
               j = j + 1
       Loop
       Next i 
    End With
End If

'
End Sub

 

[RoryA]

Which line is actually highlighted by the debugger?