How can I run this on every sheet? and more

[shadow12345]

Hi,

I have the below code which does exactly what I want but I have to run it loads of times.

What I would like to do is run the below code on:
For Every sheet in the workbook. (not all have charts)
Every Chart on those sheets (max 2 per sheet)
And every series in those charts

And create one master list of all of the Series name and Values.

Can anyone help?

Sub chartcheck()

Dim txt As String, Caption As String
    Dim ChtSeries As New ChartSeries
    With ChtSeries
        'specify Chart object and series number
        .Chart = ActiveChart
        .ChartSeries = 1
        
'This gives the name of the series
Range("s2").Value = .SeriesName.Address
Range("t2").Value = .Values.Address
    End With


End Sub

 

[Ralajer]

Shadow,

This is probably not exactly what you need but I think it has all the components.

Sub checkChart()
    Dim ChtSeries As New ChartSeries
    Dim iRow As Integer
    Dim iSeries As Integer
    Dim ws As Worksheet
    Dim Cht As ChartObject
    Dim masterOutput As Range
    
    Set masterOutput = Sheets("Master List").Range("A2")
    
    For Each ws In Worksheets
        For Each Cht In ws.ChartObjects
            For iSeries = 1 To Cht.Chart.SeriesCollection.Count
                With ChtSeries
                    'specify Chart object and series number
                    .Chart = Cht.Chart
                    .ChartSeries = iSeries
                    'This gives the name of the series
                    masterOutput.Offset(iRow, iSeries * 2 - 2).Value = .SeriesName
                    masterOutput.Offset(iRow, iSeries * 2 - 1).Value = .Values.Address
                End With
            Next iSeries
        iRow = iRow + 1
        Next Cht
    Next ws
End Sub

I wasn't sure how you wanted to handle the output so I had it output to a sheet called Master List starting in A2, grouping all the series per chart on the same row. Let me know if it works for you.

 

[shadow12345]

It seems to work however is it possible to change it slighly?

I just want to adjust the output so it shows all of the values in just two cols (A and B) rather than one row per chart.

So it would be
masterOutput.Offset(iRow, iSeries * 2 - 2).Value = .SeriesName
goes in a2

masterOutput.Offset(iRow, iSeries * 2 - 1).Value = .Values.Address
Goes in b2



next chart
masterOutput.Offset(iRow, iSeries * 2 - 2).Value = .SeriesName
goes in a3

masterOutput.Offset(iRow, iSeries * 2 - 1).Value = .Values.Address
Goes in b3

And so on....

 

[Ralajer]

Two things have to change the output to cells

masterOutput.Offset(iRow, 1).Value = .SeriesName
masterOutput.Offset(iRow, 2).Value = .Values.Address

and iRow assignment has to be moved inside the iSeries for loop

                End With
                iRow = iRow + 1
            Next iSeries
        Next Cht

 

[shadow12345]

See that above this post.... that is pure genius.


Works perfectly, thanks very much.

 

[Ralajer]

Thanks for the feedback and I'm glad to hear that it works.