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

Please explain "move fields to values", it doesn't really make sense... — vacip
Essentially, I want to move the same fields for each pivot table over to the values area using VBA. "Active" meaning the active cell within the pivot table. In other words, I've clicked on a cell within the pivot table I want to use the macro for since there are multiple pivot tables on a single worksheet. — roncruiser
I'm very new to VBA and learning on the fly. I believe I know how to move the fields over to the value area, but I do not know how to properly reference the pivot table name within VBA without going in and changing the name manually. — roncruiser

Tim Williams Tim Williams · Accepted Answer · 2016-03-30T05:49:57

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

Active Pivot Table Name

1 Answers