CODEWITHSUNDEEP
excelvbaloopspivot
Ragnar
Ragnar

Reputation: 11

VBA loop, select next entry in pivot table

I have a pivot table where I want to select the first entry, run a macro, then select the next entry and run the macro again and so forth until the list is done.

I have a criteria called "entity name" it is a Report Filter, there are around 30 different entities. I tried to select the name from the database on the next sheet:

Okay, here is the code I have so far, buts its not working as intended. I tested it without the Pivot loop and all was good, however I have to loop through the pivot table manually atm :

 Dim pt As PivotTable
        Dim pf As PivotField
        Dim pi As PivotItem

        Set pt = Sheet2.PivotTables("PivotTable14")
        Set pf = pt.PivotFields("Entity Name")

        For Each pi In pf.PivotItems



       Windows("SOW.xlsm").Activate

    Sheets(1).Copy After:=Sheets(Sheets.Count)




        Windows("2Copy of Coalition FY17 Database - Global Wallet - Switzerland.xlsx").Activate
        Range("C41:J79").Copy
        Windows("SOW.xlsm").Activate
        Range("D5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False



   Next pi

End Sub

Upvotes: 1

Views: 2598

Answers (2)

QHarr
QHarr

Reputation: 84465

Here is similar method

Option Explicit

Public Sub AllFilterValues()

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim pvt As PivotTable

    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Sheet2")
    Set pvt = ws.PivotTables("PivotTable14")

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Dim pvtField As PivotField
    Dim item As Long, item2 As Long

    Set pvtField = pvt.PivotFields("Entity Name")

    For item = 1 To pvtField.PivotItems.Count

          pvtField.PivotItems(item).Visible = True

          For item2 = 1 To pvtField.PivotItems.Count

              If item2 <> item Then pvtField.PivotItems(item2).Visible = False

          Next item2

         'Your other sub call goes here

    Next item

    Application.ScreenUpdating = True
    Application.Calculation = xlAutomatic

End Sub

Upvotes: 1

Olly
Olly

Reputation: 7891

Here's some fairly generic code for looping through all the items that exist in a specified page field:

Sub LoopPivotPageFields()
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem

    Set pt = Sheet1.PivotTables("PivotTable1")
    Set pf = pt.PivotFields("Entity Name")

    For Each pi In pf.PivotItems
        'Do something with the pivot item
        Debug.Print "Do something with pivot item " & pi.Name
        ' For example: set page filter
        pf.ClearAllFilters
        pf.CurrentPage = pi.Name
        Debug.Print "Pivot table is filtered to page item: " & pi.Name
    Next pi
End Sub

Replace the 'actions' with whatever you need to do with each item...

Upvotes: 0

Related Questions