VBA: Rename Pivot Chart using name of Worksheet

JeffHaas

New Member
Joined
Nov 29, 2016
Messages
18
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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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:

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
 
Upvote 0

Forum statistics

Threads
1,224,609
Messages
6,179,875
Members
452,949
Latest member
Dupuhini

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