Renaming Chart Title as the sheet name and text

**jshaw82** · 02-22-2013, 06:19 AM

I have a workbook with one chart on each page.
I'm trying to get the chart title to be defined as the worksheet name and the word "Spectra"

So for the sheet name "Peak 1" the chart title would be Sheetname &" Spectra"

I know I'm going to have to define the sheet name in the code but I dont know how

Has anyone got any ideas?

Thanks
John

**romperstomper** · 02-22-2013, 06:58 AM

Do you need this added to existing code that creates the chart, or a separate routine? If the former, can you post the code you have?

**jshaw82** · 02-22-2013, 08:04 AM

Please Login or Register  to view this content.

Unfortunately I cannot get the Macro to work from the start so I have to create the charts manually then run the macro to format them as I need them. I then for some bizarre reason have to format them manually as I recorded a macro to Add error bars but it doesn't work. It runs thorough but doesn't add the bars or apply the formatting in the macro. The full very untidy code is below:

Please Login or Register  to view this content.

**romperstomper** · 02-22-2013, 10:05 AM

Can you post a sample workbook for testing? I can't see any reason why your code couldn't do all of it in one go with some tweaking and tidying.

**jshaw82** · 02-22-2013, 10:22 AM

Full Bore Live 1.xls

What I want it to do is run through all 10 "peak" sheets in this book. The reason i'm trying to automate it is because there is about 20 books each with 10 worksheets that need this chart creating!

Thanks if you can get this to work!

**romperstomper** · 02-22-2013, 10:59 AM

How's this?

Please Login or Register  to view this content.

**jshaw82** · 02-22-2013, 01:04 PM

That is fantastic - the other bit that I could never get to work was this code that I recorded from a Macro

Please Login or Register  to view this content.

They're all meant to look like the chart in this file Peak 5.xls- this code formats as such and the last but is meant to delete the X error bars but it deletes the whole thing.
If it can be incorporated into the code that you have supplied it would be great but if not i'll just edit the charts manually.

Cheers John

**jshaw82** · 02-22-2013, 01:13 PM

So i've come up with this, which works:

Please Login or Register  to view this content.

I'll add this to your code and it should be good to go, unless you think it could be tidier?

**jshaw82** · 02-22-2013, 01:19 PM

In fact I will need your help in incorporating it.

**xlbiznes** · 02-22-2013, 02:02 PM

Hi,

copy paste the code below in any one of your sheet (vba).

hope this helps.

Happy Computing.

Please Login or Register  to view this content.

**jshaw82** · 02-22-2013, 06:02 PM

Ok, i've messed around and made:

Please Login or Register  to view this content.

and added

Please Login or Register  to view this content.

To the end of your code and all seems okay so far - thanks for your support today romperstomper.
.

Renaming Chart Title as the sheet name and text

LinkBack

Thread Tools

Rate This Thread

Display

Renaming Chart Title as the sheet name and text

Re: Renaming Chart Title as the sheet name and text

Re: Renaming Chart Title as the sheet name and text

Re: Renaming Chart Title as the sheet name and text

Re: Renaming Chart Title as the sheet name and text

Re: Renaming Chart Title as the sheet name and text

Re: Renaming Chart Title as the sheet name and text

Re: Renaming Chart Title as the sheet name and text

Re: Renaming Chart Title as the sheet name and text

Re: Renaming Chart Title as the sheet name and text

Re: Renaming Chart Title as the sheet name and text

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions