Active Pivot Table Name

Question

I have a worksheet with multiple Pivot Tables. All the pivot tables are formatted the same way.

How do I move multiple fields to values using VBA for active pivot table in the worksheet?

I tried recording a macro to move the fields to values but end up having to manually change the pivot table name in the VBA code when I want to use the macro for another pivot table. I'd like to avoid having to go in and change the pivot table name manually.

You can see in the screen capture, I manually drag the "Value" in the field list to the "VALUES" area for each of the pivot tables.

It's a repetitive task I'd like to automate using VBA but each of the pivot tables have unique names.

Excel Screen Capture

Answer 1

See http://peltiertech.com/referencing-pivot-table-ranges-in-vba/ for a good overview of how to address the various parts of a pivot table.

This should get you started:

Sub Tester()
    
    Dim pt As PivotTable
    
    Set pt = ActivePivotTable
    
    If pt Is Nothing Then
        Debug.Print "No Active pivot table"
    Else
        Debug.Print "Active = " & pt.Name
        'work on pt...
    End If

End Sub

'get the pivot table which contains the current selection
Function ActivePivotTable() As PivotTable
    Dim pt As PivotTable
    For Each pt In ActiveSheet.PivotTables
        Debug.Print pt.Name, pt.TableRange2.Address
        If Not Intersect(Selection, pt.TableRange2) Is Nothing Then
            Set ActivePivotTable = pt
            Exit Function
        End If
    Next pt
End Function