VBA: Rename Pivot Chart using name of Worksheet

[JeffHaas]

Hi,

I've got a macro that will take data from a worksheet, generate a Pivot Table and then create a Pivot Chart. This works fine (and thanks for the help in the past!)

Currently the Pivot Chart ends up with a generic name. I would like to rename the Pivot Chart with the name of the company, which comes from the initial worksheet.

Once the macro has run, I have worksheet 2 with the original data, and worksheet 1 with the pivot chart and table. The worksheet with the initial data on it has a name like:
Company_A_daily_report_7802a

I would like to:
- Get the name of the worksheet with the data
- Trim the name of the worksheet, to get rid of "daily_report_7802" (or whatever the number at the end is). Sometimes the word "report" is truncated to "repo" or similar.
- Use the shorter worksheet name to rename the Pivot Chart, which was created on Worksheet 1.

Thank you for any help!

Jeff

 

[JeffHaas]

Ok, I got it. This requires three VBA steps:

- Store the desired worksheet name as a string variable
- Trim unneeded characters from end of the string
- Change the name of the pivot chart.

Seemed hard but actually turned out to be easy. Code:

Sub Pivot_chart_rename()
'
' Select second sheet

    Sheets(2).Select

' Store second sheet name as variable:

    Dim strSheetName As String
    strSheetName = ActiveSheet.Name
    
' Trim off unneeded characters from end
    strExceptLast3 = Left(strSheetName, Len(strSheetName) - 6)

' Original example:
'    strExceptLast3 = Left(strToExtractFrom, Len(strToExtractFrom) - 3)

' Go back to pivot table sheet

    Sheets(1).Select

' Change name of Pivot chart
    ActiveChart.ChartTitle.Select
    ActiveChart.ChartTitle.Text = strExceptLast3

End Sub