List Chart Series Name & Formula for each chart on a new worksheet

[bubbapost]

Hello,

I have a worksheet full of charts and I have another worksheet where I want to list each chart name, chart series name, and chart series formula for reference.

Here is what I have so far:

Sub ListChartInfo()
Dim i As Integer
Dim wb As Workbook
Dim wsCharts As Worksheet
Dim wsList As Worksheet
Dim cht As Chart
Dim ChtSer As Series

Set wb = ThisWorkbook
Set wsCharts = wb.Worksheets("Charts")
Set wsList = wb.Worksheets("List")

'setup chart list worksheet
wsList.Select
Cells(1, 1) = "Chart Name"
Cells(1, 2) = "Series Name"
Cells(1, 3) = "Series Formula"
Range("A1:A3").Font.Bold = True

'Get chart series data
wsCharts.Select

For Each cht In wsCharts.ChartObjects
    i = 2
    wsList.Cells(i, 1) = cht.Name
    wsList.Cells(i, 2) = ChtSer.Name
    wsList.Cells(i, 3) = ChtSer.Formula
    i = i + 1
Next cht

End Sub

Please Help!

 

[AlphaFrog]

Move i = 2 out of the chart loop. Otherwise, it resets to 2 for each chart.

[COLOR="#FF0000"]i = 2[/COLOR]
For Each cht In wsCharts.ChartObjects
    wsList.Cells(i, 1) = cht.Name
    wsList.Cells(i, 2) = ChtSer.Name
    wsList.Cells(i, 3) = ChtSer.Formula
    i = i + 1
Next cht

 

[bubbapost]

Move i = 2 out of the chart loop. Otherwise, it resets to 2 for each chart.

[COLOR=#ff0000]i = 2[/COLOR]
For Each cht In wsCharts.ChartObjects
    wsList.Cells(i, 1) = cht.Name
    wsList.Cells(i, 2) = ChtSer.Name
    wsList.Cells(i, 3) = ChtSer.Formula
    i = i + 1
Next cht

This is Great however, it gets stuck on this:

wsList.Cells(i, 2) = ChtSer.Name
wsList.Cells(i, 3) = ChtSer.Formula

I get a Run-time error '91' Object Variable or With block variable not set.

 

[AlphaFrog]

[color=darkblue]Sub[/color] ListChartInfo()
    [color=darkblue]Dim[/color] i      [color=darkblue]As[/color] [color=darkblue]Integer[/color]
    [color=darkblue]Dim[/color] wb     [color=darkblue]As[/color] Workbook
    [color=darkblue]Dim[/color] wsCharts [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] wsList [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] cht    [color=darkblue]As[/color] ChartObject


    [color=darkblue]Set[/color] wb = ThisWorkbook
    [color=darkblue]Set[/color] wsCharts = wb.Worksheets("Charts")
    [color=darkblue]Set[/color] wsList = wb.Worksheets("List")


    [color=green]'setup chart list worksheet[/color]
    [color=darkblue]With[/color] wsList.Range("A1:C1")
        .Value = Array("Chart Name", "Series Name", "Series Formula")
        .Font.Bold = [color=darkblue]True[/color]
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
    i = 2
    [color=darkblue]For[/color] [color=darkblue]Each[/color] cht [color=darkblue]In[/color] wsCharts.ChartObjects
        wsList.Cells(i, 1) = cht.Name
        [color=darkblue]With[/color] cht.Chart.SeriesCollection(1)
            wsList.Cells(i, 2) = .Name
            wsList.Cells(i, 3) = "'" & .Formula
        [color=darkblue]End[/color] [color=darkblue]With[/color]
        i = i + 1
    [color=darkblue]Next[/color] cht


[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[bubbapost]

Thank you AlphaFrog!

This works perfectly!