Reset Pivot Table Name/Count?

A

ai1094

Board Regular
Joined
Aug 23, 2018
Messages
92
Hello, I was wondering if there was a way to reset the Pivot Table name after deleting a sheet containing a pivot and creating a new sheet with a pivot.

For Example:

I open up Excel for the first time and create a pivot table from my raw data. A new sheet is created with the pivot table named "PivotTable1" and if I delete that sheet and create a new sheet with a pivot it will be called "PivotTable2" and if I delete that sheet and create ANOTHER new pivot it will be named "PivotTable3" and so on.

Is there anyway (without closing Excel and opening it again) to reset the count/name every time I create a new sheet for a pivot so it's back in sequential order, i.e. "PivotTable1"?
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
In a word, no. Why does it matter?
 
Upvote 0
Because I have a macro involving duplicating multiple pivot tables in one sheet, which works to a certain extent but I am not able to filter each of the pivot tables because the pivot table names change.
 
Upvote 0
A macro should be able to either rename them, or work without the names.
 
Upvote 0
This is my current code. If I open the file in excel for the first time, it works. But if I delete the sheet and run it, it'll work, but it won't change the filter/table style for each pivot table. This macro just copies the existing pivot table into the same sheet, under a few rows. The number of rows won't ever change, which is why I am doing it this way.

Code: 
Sub Pivot()


    Application.ScreenUpdating = False
    Dim shtSrc As Worksheet, shtDest As Worksheet
    Dim pc As PivotCache
   
    Set shtSrc = ActiveSheet


    Set shtDest = shtSrc.Parent.Sheets.Add()


    Set pc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
        SourceData:=shtSrc.Cells(1).CurrentRegion.Address)
    pc.CreatePivotTable TableDestination:=shtDest.Range("A3"), _
        TableName:="PivotTable1"


    With shtDest.PivotTables("PivotTable1")
        .InGridDropZones = True
        .RowAxisLayout xlTabularRow
    End With


''''''''''''''''''''''''''''''''''''''''''''''''''''


 With ActiveSheet.PivotTables("PivotTable1").PivotFields("BUILDING_LOCID")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("OPTION_CODE")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("OPTION_STATUS_NAME")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("LINE_ITEM_TYPE")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("LINEITEM")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("C_YEAR")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("ACTUALS_RO"), "Sum of ACTUALS_RO", xlSum
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of ACTUALS_RO")
        .NumberFormat = "$#,##0.00"
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("LINEITEM")
        .PivotItems("CS Total - P&L").Visible = False
    End With
    ActiveSheet.PivotTables("PivotTable1").PivotFields("OPTION_STATUS_NAME"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable1").PivotFields("OPTION_STATUS_NAME"). _
        CurrentPage = "Forecast"
    ActiveSheet.PivotTables("PivotTable1").PivotFields("LINE_ITEM_TYPE"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable1").PivotFields("LINE_ITEM_TYPE"). _
        CurrentPage = "P&L"
    Range("A1:H15").Select
    Selection.Copy
    Range("A18").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Range("A35").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWindow.SmallScroll Down:=24
    Selection.Copy
    Range("A52").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWindow.SmallScroll Down:=9
    Selection.Copy
    Range("A69").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWindow.SmallScroll Down:=-51
    ActiveSheet.PivotTables("PivotTable2").PivotFields("OPTION_CODE"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable2").PivotFields("OPTION_CODE").CurrentPage _
        = "ACT"
    Range("A18").Select
    ActiveSheet.PivotTables("PivotTable2").TableStyle2 = "PivotStyleLight17"
    ActiveWindow.SmallScroll Down:=9
    ActiveSheet.PivotTables("PivotTable3").PivotFields("OPTION_CODE"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable3").PivotFields("OPTION_CODE").CurrentPage _
        = "AUTO RENEWAL"
    Range("A35").Select
    ActiveSheet.PivotTables("PivotTable3").TableStyle2 = "PivotStyleLight18"
    ActiveWindow.SmallScroll Down:=21
    ActiveSheet.PivotTables("PivotTable4").PivotFields("OPTION_CODE"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable4").PivotFields("OPTION_CODE").CurrentPage _
        = "RO"
    Range("B53").Select
    ActiveSheet.PivotTables("PivotTable4").TableStyle2 = "PivotStyleLight19"
    ActiveWindow.SmallScroll Down:=15
    Range("B70").Select
    ActiveSheet.PivotTables("PivotTable5").TableStyle2 = "PivotStyleLight15"
    ActiveSheet.PivotTables("PivotTable5").PivotFields("Sum of ACTUALS_RO"). _
        Orientation = xlHidden
    ActiveSheet.PivotTables("PivotTable5").AddDataField ActiveSheet.PivotTables( _
        "PivotTable5").PivotFields("FORECAST"), "Count of FORECAST", xlCount
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("Count of FORECAST")
        .Caption = "Sum of FORECAST"
        .Function = xlSum
        .NumberFormat = "$#,##0.00"
    End With
    ActiveWindow.SmallScroll Down:=-72
    Cells.Select
    Selection.Columns.AutoFit
    Range("A1").Select
    


End Sub
 
Last edited by a moderator:
Upvote 0
You should be able to do something like this (though personally I would probably just recreate the pivot each time):

Code: 
Sub Pivot()

    Application.ScreenUpdating = False
    Dim shtSrc As Worksheet, shtDest As Worksheet
    Dim pc As PivotCache, pt As PivotTable

    Set shtSrc = ActiveSheet


    Set shtDest = shtSrc.Parent.Sheets.Add()

    Set pc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
                                               SourceData:=shtSrc.Cells(1).CurrentRegion.Address)
    Set pt = pc.CreatePivotTable(TableDestination:=shtDest.Range("A3"))

    With pt
        .InGridDropZones = True
        .RowAxisLayout xlTabularRow
        With .PivotFields("BUILDING_LOCID")
            .Orientation = xlPageField
            .Position = 1
        End With
        With .PivotFields("OPTION_CODE")
            .Orientation = xlPageField
            .Position = 1
        End With
        With .PivotFields("OPTION_STATUS_NAME")
            .Orientation = xlPageField
            .Position = 1
        End With
        With .PivotFields("LINE_ITEM_TYPE")
            .Orientation = xlPageField
            .Position = 1
        End With
        With .PivotFields("LINEITEM")
            .Orientation = xlRowField
            .Position = 1
        End With
        With .PivotFields("C_YEAR")
            .Orientation = xlColumnField
            .Position = 1
        End With
        .AddDataField .PivotFields("ACTUALS_RO"), "Sum of ACTUALS_RO", xlSum
        .PivotFields("Sum of ACTUALS_RO").NumberFormat = "$#,##0.00"
        .PivotFields("LINEITEM").PivotItems("CS Total - P&L").Visible = False
        .PivotFields("OPTION_STATUS_NAME").ClearAllFilters
        .PivotFields("OPTION_STATUS_NAME").CurrentPage = "Forecast"
        .PivotFields("LINE_ITEM_TYPE").ClearAllFilters
        .PivotFields("LINE_ITEM_TYPE").CurrentPage = "P&L"
    End With
    Range("A1:H15").Copy
    Range("A18").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Range("A35").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Range("A52").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Range("A69").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    With shtDest.PivotTables(2)
        .PivotFields("OPTION_CODE").ClearAllFilters
        .PivotFields("OPTION_CODE").CurrentPage = "ACT"
        .TableStyle2 = "PivotStyleLight17"
    End With
    With shtDest.PivotTables(3)
        .PivotFields("OPTION_CODE").ClearAllFilters
        .PivotFields("OPTION_CODE").CurrentPage = "AUTO RENEWAL"
        .TableStyle2 = "PivotStyleLight18"
    End With
    With shtDest.PivotTables(4)
        .PivotFields("OPTION_CODE").ClearAllFilters
        .PivotFields("OPTION_CODE").CurrentPage = "RO"
        .TableStyle2 = "PivotStyleLight19"
    End With
    With shtDest.PivotTables(5)
        .TableStyle2 = "PivotStyleLight15"
        .PivotFields("Sum of ACTUALS_RO").Orientation = xlHidden
        .AddDataField .PivotFields("FORECAST"), "Sum of FORECAST", xlSum
        .PivotFields("Sum of FORECAST").NumberFormat = "$#,##0.00"
    End With
    Cells.Select
    Selection.Columns.AutoFit
    Range("A1").Select

End Sub
 
Upvote 0
Change the name of your module so it’s not PivotTable!
 
Upvote 0
One way, if I understand the original question,
from the pivot table right click then choose pivot table options
at the very top of the dialog box edit the name to what you want

Such as change from PivotTable2 to PivotTable1
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Solved
Multiple pivot tables on the same sheet
Replies
2
Views
271
Woftae
W
C
  • Question
Confused over pivot table calculations :-(
Replies
1
Views
239
number kruncher
N
A
  • Question
Two dimensional dynamic named range over a pivot table not working as I thought
Replies
4
Views
261
ashleighbrown
A
M
  • Question
Excel pivot table query - retaining filters on pivot in absence of data
2
Replies
10
Views
967
Alex Blakenburg
A
A
  • Solved
Pivot Table Sum giving Zeros but count works
Replies
5
Views
560
RoryA
RoryA

Forum statistics

Threads
1,214,611
Messages
6,120,510
Members
448,967
Latest member
screechyboy79

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top