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

Gersie

New Member
Joined
Sep 7, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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?

VBA 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
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,767
Office Version
  1. 2016
Platform
  1. Windows
Not sure if it is just me but where is Line 22? 😁
 

Gersie

New Member
Joined
Sep 7, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,562
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
The code as posted wouldn't run as it's missing a few parentheses.
 

Gersie

New Member
Joined
Sep 7, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

VBA 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
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,562
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You should get a 438 error there since the property is EnableMultiplePageItems.
 

Gersie

New Member
Joined
Sep 7, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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

VBA 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.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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,562
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Which line is actually highlighted by the debugger?
 

Forum statistics

Threads
1,144,162
Messages
5,722,847
Members
422,460
Latest member
VBA_Noob01

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top