Making Graph Tab Name = Graph Title

[Centrican]

I'm wondering if there is a way to make the tab name for a graph be the same as the graph title. The title of my 75 graphs (yes, I know it's a lot) depends on the data in my Data sheet, but I want the name of the tabs to change automatically if the data changes.

Is it possible? I'm guessing probably not very easily as it's a worksheet name instead of a formula.

 

[Andrew Poulsom]

You could use this macro:

Sub Test()
    Dim Ch As Chart
    For Each Ch In ActiveWorkbook.Charts
        Ch.Name = Ch.ChartTitle.Caption
    Next Ch
End Sub

There is no error checking so the code will fail if the sheet name already exists.

 

[SydneyGeek]

Let's make some assumptions:

1. In all cases, the cell that your chart title refers to is the same on each sheet
2. You are using Excel 2003 or lower, so this will work
3. You are using embedded charts, not chart sheets

I have used F1 as the location for each chart title. Place this code in the This Workbook module (Alt+F11 to get to the macro environment, right-click This Workbook and View>Code, then Paste)

Now, go to one of the sheets with a chart and enter something in F1. The sheet name will change. OK -- now to make that cell the chart title.

1. Select the chart, then select the chart title.
2. Press =, then select cell F1 and press Enter. Your chart title is now the same as cell F1, which (because of the code) will be the worksheet name.

Denis

 

[Centrican]

Thanks for that code Andrew - that's perfect. There's no worry about duplicate tabs, unless the co-op have a store called 'Data' that won't be an issue. If it is I can always add some checking.

SydneyGeek; Sorry for this; but the three assumptions aren't true in my case (I'm using office 2003 to make the graphs, but I want to make sure my method will work under 2007 too).

Your advice about making the title equal cell F1 is spot on, except that you have to make sure it's in the equasion bar, not the chart title box itself but I knew what you meant. Thanks for the input.



Another quick question before I spend the afternoon making graphs: Is there a way to create 60 odd graphs with titles equalling F2, G2, H2, I2 etc.?

The range of the graph will also change from F3:F338 to G3:G338, H3:H338 etc. as well.

 

[Andrew Poulsom]

See if you can adapt this:

Sub Test()
'   Change constants to suit
    Const FirstRow As Integer = 3
    Const LastRow As Integer = 338
    Const ChartCount As Integer = 7
    Dim Sh As Worksheet
    Set Sh = Worksheets("Sheet1")
    Dim x As Integer
    For x = 6 To ChartCount
        Charts.Add
        With ActiveChart
            .ChartType = xlColumnClustered
            .Location Where:=xlLocationAsNewSheet
            .SeriesCollection.NewSeries
            .SeriesCollection(1).XValues = "=" & Sh.Name & "!R" & FirstRow & "C1:R" & LastRow & "C1"
            .SeriesCollection(1).Values = "=" & Sh.Name & "!R" & FirstRow & "C" & x & ":R" & LastRow & "C" & x
            .HasTitle = True
            .ChartTitle.Characters.Text = Sh.Cells(2, x).Value
            .HasLegend = True
            .Legend.Position = xlRight
        End With
    Next x
End Sub

 

[Centrican]

This is really great, Andrew. Thanks a lot.

Is there anywhere with a list of all the variables for a chart? As I need to play with this quite a bit to get it formatting correctly.

I'll need two series using slightly different data sources, and using different X axes, and I wanna format it a bit too. It shouldn't be too hard to figure out, and the best part is it is able to deal with any number of tables if I use a 'columncount' calculation as well.

Thanks again, you are a star!

 

[Andrew Poulsom]

Record a macro while creating a chart and formatting it as you want. Then add the relevant parts to the code I posted.