CODEWITHSUNDEEP
excelvbafor-looppivot-tablenested-for-loop
Laira Reeba Joseph
Laira Reeba Joseph

Reputation: 1

How to use nested for loop that loops through pivot tables in VBA?

I am trying to use a nested for loop that parses through all the worksheets (1st For Loop) and parses through each Pivot table in each worsksheets (2nd For loop). Within 2nd For loop, I am trying to change the pivot filter value based on a combobox selection.

Below is the code, but it does not loop through the 2nd for loop.

Private Sub bo_combobox_Change()
Dim a As String
Dim pt As PivotTable
Dim ws As Worksheet

    If bo_combobox.Value <> "" Then       

        For Each ws In ActiveWorkbook.Worksheets

            For Each pt In ThisWorkbook.PivotTables
                With pt.PivotFields("Objective")
                    .ClearAllFilters
                    .CurrentPage = bo_combobox.Value
                    Debug.Print (.CurrentPage)
                End With
            Next pt

        Next ws

    End If   

End Sub

Upvotes: 0

Views: 461

Answers (1)

chris neilsen
chris neilsen

Reputation: 53125

Your (asked about) problem is with

For Each pt In ThisWorkbook.PivotTables

The Workbook.PivotTables collection is not what it seems:

From the documentation

The PivotTables property of the Workbook object does not return all the PivotTable objects in the workbook; instead, it returns only those associated with decoupled PivotCharts. However, the PivotTables method of the Worksheet object returns all the PivotTable objects on the worksheet, irrespective of whether they are associated with decoupled PivotCharts.

That said, there are several other issues

  1. You have For Each ws In ActiveWorkbook.Worksheets then don't use ws
  2. You are referenceing both ActiveWorkbook and ThisWorkbook. These may or may not be the same workbook

Your code, refactoed

Private Sub bo_combobox_Change()
    Dim a As String
    Dim pt As PivotTable
    Dim ws As Worksheet
    Dim wb as Workbook

    Set wb = ActiveWorkbook 'or ThisWorkbook, or select a book by any means you choose
    If bo_combobox.Value <> vbNullString Then       
        For Each ws In wb.Worksheets
            For Each pt In ws.PivotTables
                ' remainder of your code
                '...
            Next pt
        Next ws
    End If   
End Sub

Upvotes: 1

Related Questions