'How to get the values when multiple items are selected in PivotTable PageField?

I am rying to ge the values of PivotField in PivotTable(1).Pivotfields, then I am trying to get the the filter value to string(str- Declared as string), It is working fine, but when I select multiple items in filter, "(All)" value is passed to str.

How to pass the value of selected items(more than one) in pivot items.

 For Each PF In PT1.PivotFields
 If PF.Orientation = xlPageField Then
    str = PF.CurrentPage
 PT2.PivotFields(PF.Name).CurrentPage = str

Below is the snap of values I need to pass to str.

enter image description here

Any help will be greately appreciated.

Solution 1:^[1]

Try code below. First it checks if you have enabled MultiplePageItems in your PivotTable, if true, it check every item if it's selected (visible). You save those values in variable or do anything else.

If ActiveSheet.PivotTables("table").PivotFields("field").EnableMultiplePageItems = True Then
    For i = 1 To ActiveSheet.PivotTables("table").PivotFields("field").PivotItems.Count
        If ActiveSheet.PivotTables("table").PivotFields("field").PivotItems(i).Visible = True Then 'if selected then

        'your code

        End If

    Next i

End If

Solution 2:^[2]

ActiveSheet.PivotTables("PivotTable1").PivotFields("field").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields("field").EnableMultiplePageItems = True
For i = 1 To ActiveSheet.PivotTables("PivotTable1").PivotFields("field").PivotItems.Count
    If ActiveSheet.PivotTables("PivotTable1").PivotFields("field").PivotItems(i).Visible = True Then
    ActiveSheet.PivotTables("PivotTable1").PivotFields("field").PivotItems(i).Visible = False
    If ActiveSheet.PivotTables("PivotTable1").PivotFields("field").PivotItems("item name 1 ").Visible = False Then
    ActiveSheet.PivotTables("PivotTable1").PivotFields("field").PivotItems(i).Visible = True
    End If
    If ActiveSheet.PivotTables("PivotTable1").PivotFields("field").PivotItems("item name 2 ").Visible = False Then
    ActiveSheet.PivotTables("PivotTable1").PivotFields(field").PivotItems(i).Visible = True
    End If
   this if you repeat 3, 4, 5, etc……. 
    End If

Next i

Solution 3:^[3]

Try this:

Sub marine()
    Dim PT1 As PivotTable, PT2 As PivotTable
    Set PT1 = Sheet1.PivotTables("PivotTable1")
    Set PT2 = Sheet1.PivotTables("PivotTable2")
    Dim PF As PivotField, PI As PivotItem, myfilter
    For Each PF In PT1.PageFields '~~> directly work on PageFields
        If PF.EnableMultiplePageItems = True Then '~~> check if multi-items
            For Each PI In PF.PivotItems
                If PI.Visible = True Then
                    If IsEmpty(myfilter) Then
                        myfilter = Array(PI.Caption)
                    Else
                        ReDim Preserve myfilter(UBound(myfilter) + 1)
                        myfilter(UBound(myfilter)) = PI.Caption
                    End If
                End If
            Next
        Else
            myfilter = PF.CurrentPage
        End If
        If IsArray(myfilter) Then
            PT2.PivotFields(PF.Name).EnableMultiplePageItems = True
            For Each PI In PT2.PivotFields(PF.Name).PivotItems
                If Not IsError(Application.Match(PI.Caption, myfilter, 0)) Then
                    PI.Visible = True
                Else
                    PI.Visible = False
                End If
            Next
        Else
            PT2.PivotFields(PF.Name).CurrentPage = myfilter
        End If
    Next
End Sub

It seems you have to loop through the PT1 items to get each.
And also loop through PT2 items to select those filters identified from PT1. HTH.
Note: If however, your using XL 2010 and up, just use a Slicer.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	lowak
Solution 2	Virgilio
Solution 3

'How to get the values when multiple items are selected in PivotTable PageField?

Solution 1:[1]

Solution 2:[2]

Solution 3:[3]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]

Solution 3:^[3]