CODEWITHSUNDEEP
vbaexcelpivot-table
Lucas
Lucas

Reputation: 125

VBA PivotTable Runtime Error 5: Invalid Procedure Call or Argument

I am running a macro to update the filter fields ("Facility") of all pivot tables in the workbook when the filter field of the first PivotTable is changed (see code below). The macro run as expected until the line with .PivotTables("PivotTable5").PivotFields("Facility").CurrentPage = facility, which gives the runtime error 5.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim facility As String
'If the PivotTable1 filter value is changed...
If Not Intersect(Target, Me.PivotTables("PivotTable1").PivotFields("Facility").DataRange) Is Nothing Then
    facility = Me.PivotTables("PivotTable1").PivotFields("Facility").CurrentPage
    'Change all the other PivotTable filters to the same as PivotTable1
    With Me
        .PivotTables("PivotTable2").PivotFields("Facility").CurrentPage = facility
        .PivotTables("PivotTable3").PivotFields("Facility").CurrentPage = facility
        .PivotTables("PivotTable4").PivotFields("Facility").CurrentPage = facility
        .PivotTables("PivotTable5").PivotFields("Facility").CurrentPage = facility
        .PivotTables("PivotTable6").PivotFields("Facility").CurrentPage = facility
        .PivotTables("PivotTable7").PivotFields("Facility").CurrentPage = facility
    End With
    With ThisWorkbook
        .Worksheets("4E - Bili Screen (PivotTable)").PivotTables("PivotTable1").PivotFields("Facility").CurrentPage = facility
        .Worksheets("4E - DVT Proph (PivotTable)").PivotTables("PivotTable1").PivotFields("Facility").CurrentPage = facility            
        .Worksheets("4F - High-Risk Del (PivotTable)").PivotTables("PivotTable1").PivotFields("Facility").CurrentPage = facility
    End With
End If
End Sub

Upvotes: 0

Views: 1277

Answers (1)

Lucas
Lucas

Reputation: 125

After recording a macro while manually updating the filter on PivotTable5 (which works fine), I noticed the recorded macro inserted .ClearAllFilters prior to setting the .CurrentPage value (as shown below), which solved the issue.

.PivotTables("PivotTable5").PivotFields("Facility").ClearAllFilters
.PivotTables("PivotTable5").PivotFields("Facility").CurrentPage = facility

However, I needed to .ClearAllFilters for each PivotTable after PivotTable5, or the error would occur on the next .CurrentPage line. I don't know why the error only occured on or after .PivotTables("PivotTable5").PivotFields("Facility").CurrentPage = facility line, but not the equivalent lines above it.

Upvotes: 1

Related Questions