For Next Loop

I have some code which all runs perfectly. I needed to expand the code with a For Next Loop, and so added this, but when I run it the workbook just hangs. Have I set it into an endless loop and what do I need to do to prevent that?

I'm still very new to VBA and am adding code through trial and error but am unsure where I'm going wrong...

Added Code

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim varMySheet As Variant
    Dim pt As PivotTable, arr, rng As Range, i As Long, FName
    
    Dim oChart As ChartObject
    Dim mySrs As Series


    For Each ws In ActiveWorkbook.Worksheets
        For Each oChart In ws.ChartObjects
            For Each mySrs In oChart.Chart.SeriesCollection
                mySrs.Formula = WorksheetFunction.Substitute(mySrs.Formula, "[1]", "'My Report'")
            Next
        Next
    Next ws

Many thanks

See if setting calculation to manual makes any difference.

I don't use vba with charts so can't comment on specifics of your code, but standard excel behaviour with automatic calculation would be to recalculate every time the code passes over WorksheetFunction.

Stepping through the code with f8 and monitoring the values of your variables is sometimes the easiest way to find why your code is running slow or not as expected.

In your main code you have

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

which is used to disable a couple of things and make the code run more efficiently, another line you can use here is

Application.Calculation = xlCalculationManual

Which turns of automatic calculation, then

Application.Calculation = xlCalculationAutomatic

which turns it back on again after the code has finished.

Also, if your code is ended prematurely (for example, by killing the process when it hangs or ending the code when it errors), these settings do not automatically re-enable, you need to do it manually. These are excel settings, not workbook settings, so closing without saving will not cancel out the original change to disable them. The simple way to do this is with a small piece of code like

Sub Reset_Stuff()
With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
End Sub

Regarding the runtime error, does the workbook 'New Workbook Name.xlsx' actually exist? If that workbook is not open then you would need to specify the full workbook file path, not just the name.

See if this extract from PeltierTech (<<link to page) helps, it shows the correct way that the code should be written for what you're trying to do.

Sub ChangeSeriesFormulaAllChartsAllSheets()
    ''' Do all charts in all sheets
    Dim oWksht As Worksheet
    Dim oChart As ChartObject
    Dim OldString As String, NewString As String
    Dim mySrs As Series

    OldString = InputBox("Enter the string to be replaced:", "Enter old string")

    If Len(OldString) > 1 Then
        NewString = InputBox("Enter the string to replace " & """" _
            & OldString & """:", "Enter new string")
        For Each oWksht In ActiveWorkbook.Worksheets
            For Each oChart In oWksht.ChartObjects
                For Each mySrs In oChart.Chart.SeriesCollection
                    mySrs.Formula = WorksheetFunction.Substitute(mySrs.Formula, _
                        OldString, NewString)
                Next
            Next
        Next
    Else
        MsgBox "Nothing to be replaced.", vbInformation, "Nothing Entered"
    End If
End Sub

Hopefully this is of some use. I expect that Rory (romperstomper) will have significantly more knowledge and experience than I do relating to chart use and will (hopefully) provide further assistance.
Don't be afraid to ask if there is something you don't fully understand.

Strangely PeltierTech is where I 'borrowed' this bit of code from...

    For Each ws In ActiveWorkbook.Worksheets
        For Each oChart In ws.ChartObjects
            For Each mySrs In oChart.Chart.SeriesCollection
                mySrs.Formula = WorksheetFunction.Substitute(mySrs.Formula, "'Original Workbook Name.xlsm'", "'New Workbook Name.xlsx'")
            Next
        Next
    Next ws

With regards your question 'does the workbook 'New Workbook Name.xlsx' actually exist?' I had been thinking about this, the answer is no because the code is designed to create a new workbook and save the file with the name of the new workbook but having said that, my understanding of the code above is that all it should be doing is changing the filename for the data series, as in from:

=SERIES(" Total Forecast",{"M1","M2","M3","M4","M5","M6","M7","M8","M9","M10","M11","M12"},'Original Workbook Name.xlsm'!My_Forecast,1)

to

=SERIES(" Total Forecast",{"M1","M2","M3","M4","M5","M6","M7","M8","M9","M10","M11","M12"},'New Workbook Name.xlsx'!My_Forecast,1)

before saving the file, so does 'New Workbook.xlsx actually need to exist before the file is saved? If the answer is yes, then does that mean the code needs to sit after

    FName = Application.GetSaveAsFilename(InitialFileName:="C:\My Folder\My Report.xlsx", fileFilter:="Excel workbook (*.xlsx), *.xlsx")
    On Error Resume Next: If FName <> False Then wb.SaveAs FName
        
    If Err.Number <> 0 Then
        Application.Dialogs(xlDialogSaveAs).Show
    Err.Clear
        
    End If

I'll experiment with some of your suggestions and let you know how I get on. Many thanks for all your very helpful suggestions. It feels as though the code is 95% there but I'm just missing one vital element... I just wish I know what that vital element was.

When you run the line to update the formula (chart series) excel will attempt to create and validate the link, if there is nothing there then you will get an error, so ideally you would save the workbook first to create the valid filename, then update the charts.

You might be able to make it work with something like

mySrs.Formula = WorksheetFunction.Substitute(mySrs.Formula, "[1]", wb.Name)

but without testing I'm not certain of that.

Personally, I would save first to create the new filename and path, then work with that.