Name pivot table

[Hidden Dan]

I thought it was an easy job, but ...

I need to name my pivot table according a cell value. I currently type the name manually into the appropriate pivot table options field. But I presume a simple VBA can do the task better.

The reason is that when I copy a sheet a couple of times the pivot table keeps the same name.


So in cell D5 I read "Table net income" and subsequently my pivot table should get that name by executing a macro.


How can I do that best ?

Thanks, Dan

 

[jorismoerings]

Hi,

you could use something like this but be aware this code will assume there's only 1 pivot on the copied worksheet. If there are mote you need to adress each one individual.

Sub RenamePivot()
    With Sheets("Sheet1")
        .PivotTables(1).Name = range("D5").Value
    End With
End Sub

 

[Hidden Dan]

Joris, thanks.

There will be only one pivot table on the sheet. Actually sheets are copied once a year so sheets are named 2015 / 2016 /2017 /2018 etcetera. The year is read from a cell value and automatically the sheet is updated by grabbing data from the underlaying sheet. Therefor I need to create a simple macro that does 2 things. (1) changing pivot table name (2) updating pivot table. For the first I needed to know how to automatically change the name. Otherwise it would keep the name from the former year.

The other issues in the macro are currently present.

Dan

 

[Hidden Dan]

Hi,

you could use something like this but be aware this code will assume there's only 1 pivot on the copied worksheet. If there are mote you need to adress each one individual.

Sub RenamePivot()
    With Sheets("Sheet1")
        .PivotTables(1).Name = range("D5").Value
    End With
End Sub

I tried code and it works for 1 defined sheet. In my example my sheet is named 2017.

Sub RenamePivot()
With Sheets("2017")
        .PivotTables(1).Name = Range("D2").Value
    End With
End Sub

But when I copy sheet 2017 and rename it to 2018, script reads D2 from 2017. So I changed script to

Sub RenamePivot()
    With ActiveSheet
        .PivotTables(1).Name = Range("D2").Value
    End With
End Sub

But this adaptation doesn't work. It still reads D2 from 2017 instead of D2 from 2018 .


I thought my adapation was correct but apparently I'm wrong. What should I do now ?


Dan

 

[pvr928]

Hi Hidden Dan

The code is not working as you intended because of the start of the With statement:

With ActiveSheet

The ActiveSheet is, as the name suggests, whatever sheet is visible at the time the code gets to that line. The code references the '2017' tab because that is the active sheet at the time the code is run.

Consequently, if you want your code to reference the sheet you have most recently copied, you either need to modify the code, or manually make the sheet you want the ActiveSheet.

It sounds like you are manually copying sheets; if so, then you need to make that new sheet the ActiveSheet and then run your code. You could instead codify the creation/copying of a sheet and then have the code reference that newly created/copied sheet.

Cheers

pvr928

 

[Hidden Dan]

Hi Hidden Dan

The code is not working as you intended because of the start of the With statement:

With ActiveSheet

I made a horrible mistake. I stored macro in 'Sheet' section rather than 'Module' !!

Now this is the code I got now and it works well.

Sub RenamePivot_2()


    Dim currentName As String
    currentName = ActiveSheet.Name


    With Sheets(currentName)
        .PivotTables(1).Name = Range("D2").Value
    End With
End Sub

Thanks for helping.

Dan