Renaming Pivot Table Name via VBA

[VanceLiving]

Hello,

Wondering if it is possible to use macro to open a Pivot Table file and rename the Pivot Table Name without initially knowing what the Pivot Table Name is? I have been generating a Pivot Table file which has a random Pivot Table Name affixed to each time. In my current process, I open the file and change the Pivot Table Name to "PivotTable1" each time before proceeding. Hoping there is a way to eliminate this step. Certainly appreciate any help - thanks!!

 

[Jerry Sullivan]

Hi and Welcome to the Board,

That's easier to do if there is only one PivotTable on the specified sheet as it sounds like you have.

Sub RenameSinglePivot()
'--Assumes there is only one PivotTable on specified Sheet
    With Sheets("Sheet1")
        .PivotTables(1).Name = "PivotTable1"
    End With
End Sub

If you have more than one PivotTable on the sheet, you need to address:
1. Referencing the correct PivotTable
2. Ensuring that there is not already a different PivotTable on the same sheet with that name.

 

[VanceLiving]

That did it - thanks so much!!

 

[sourabh_ajmera]

I have dobut on this. What if you want to name a pivot table to say "abcPivotTable" while creating a pivot table and not renaming it, so the default name stays as "abcPivotTable" rather than PivotTables(1), PivotTables(2) and so on?

 

[sg2209]

How do we change it when we have 4 pivots in the same sheet ?

 

[SahmedS]

Sub Change_PivotTable_Name()
Dim pt as pivottable
Dim wsp as worksheet
set wsp = Thisworkbook.sheets(1)
For Each pt In wsp.PivotTables
pt.Name = "PivotTable" & " " & Format(Now, "mm/dd/yy HH:mm:ss")
Next pt
End Sub

 

[CrazyBritGuy]

Another approach which will rename the Pivot Table the same as the Worksheet:

Sub getPVData()
Dim ws      As Worksheet
Dim pts     As PivotTables
Dim pt      As PivotTable
Dim pf      As PivotField
Dim ptRng   As Range

Set ws = ActiveSheet
Set pts = ActiveSheet.PivotTables
If pts.Count < 1 Then Exit Sub
Set pt = ws.PivotTables(1)
pt.Name = ws.Name

'* All the currently VISIBLE data in the pivot table
Set rng = pt.TableRange1
End Sub