Check Charts on Multiple Sheets

[squnibi]

I have a code that will check charts and force update them. I need to update the code to go through all charts on several worksheets. I'm assuming I need to create a loop but I'm not too sure how. Can someone help? Here is the code:

Sub ManuallyUpdateAllCharts()

Dim ch As ChartObject
Dim cht As Chart
Dim sc As Series
Dim strValues$, strXValues$
Dim i&
Dim strProblems$()
Dim lProblems&
Dim strReport$

lProblems = 0
strReport = ""

For Each ch In Worksheets("Sheet1").ChartObjects
'    MsgBox ch.Name
    Set cht = ch.Chart
    For Each sc In cht.SeriesCollection
        On Error Resume Next
        i = LBound(sc.Values)
        If Err.Number = 0 Then
            strValues = ParseRangeFromChartFormula(sc.Formula, True)
            strXValues = ParseRangeFromChartFormula(sc.Formula, False)
            If Left(strXValues, 1) <> "(" And Left(strValues, 1) <> "(" Then
                If CheckChartAgainstSheet(sc.Values, strValues$, sc.XValues, strXValues$) Then
                    lProblems = lProblems + 1
                    If lProblems = 1 Then
                        ReDim strProblems(1 To 1)
                        If cht.HasTitle Then
                            strProblems(1) = cht.ChartTitle.Text & ":  Series # " & i
                        Else
                            strProblems(1) = cht.Name & ":  Series # " & i
                        End If
                    Else
                        ReDim Preserve strProblems(1 To lProblems)
                        If cht.HasTitle Then
                            strProblems(lProblems) = cht.ChartTitle.Text & ":  Series # " & i
                        Else
                            strProblems(lProblems) = cht.Name & ":  Series # " & i
                        End If
                    End If
                    sc.Formula = sc.Formula
                    GoTo ErrorBreak
                End If
            End If
        End If
        Err.Clear
        On Error GoTo 0
ErrorBreak:
    Next sc
Next ch

If lProblems > 0 Then
    strReport = "There are " & lProblems & " charts with data that do not match the spreadsheet:"
    For i = 1 To lProblems
        strReport = strReport & vbCrLf & strProblems(i)
    Next i
    strReport = strReport & vbCrLf & vbCrLf & "The macro made an attempt to correct these errors, but you should check to confirm."
    'MsgBox strReport, vbCritical
End If

End Sub

 

[Rasm]

You are missing the loop below - so I would start with it as first loop - the do the for each chart loop - this assumes you only have one workbook open.

For j = 1 To Sheets.Count
 
 
 
Your code
 
 
Next J

 

[Rasm]

You also need a chart count - right now you only have every series on a single chart - at least thats what it looks like

So you need another loop

NumCharts = ActiveSheet.ChartObjects.Count 'This will tell you how many charts you have in activesheet (you can replace that with sheet(J)