Trying to adjust series of graph's formulae/ranges to activesheet name

Question

My goal is to have a macro that changes each graph in my workbook. The ranges of each series in the graph needs to be changed to its own worksheet. I have tried replacing a portion of each series .formula by its active sheet name.

After looking into it for quite a while, I got stuck with run-time error "1004:Application-defined or object-defined error." I haven't gotten much further since, anyone knows what might be the cause of this? Any help would be very much appreciated.

The cycling does function, the error only occurs when the conditions are met for the If statement executing srsFullSeriesCollection.Formula = Bname.

Sub Chartchanger()

    Dim cht As ChartObject
    Dim sht As Worksheet
    Dim srsFullSeriesCollection As Series
    Dim xName As String
    Dim Bname As String
    Dim CurrentSheet As Worksheet
        
Set CurrentSheet = ActiveSheet

'Cycling through sheets, Charts, and Chart series'
            For Each sht In ActiveWorkbook.Worksheets
            sht.Activate
            xName = ActiveSheet.Name
                For Each cht In sht.ChartObjects
                cht.Activate
                   For Each srsFullSeriesCollection In ActiveChart.FullSeriesCollection()
'Performing the operation on select series that contain "Sheet1" in their formula'
'"Sheet1" does not have any charts on it, I used it only as a test for charts on other sheets'
                    If InStr(1, srsFullSeriesCollection.Formula, "Sheet1") > 0 Then
                    Bname = srsFullSeriesCollection.Formula
                    Bname = Replace(Bname, "Sheet1", xName)
                    srsFullSeriesCollection.Formula = Bname
                    End If
                  Next srsFullSeriesCollection
               Next cht
            Next sht
CurrentSheet.Activate
         
    End Sub

Currently, I'm looking into changing the ranges of the series instead of the formula. I have no real experience working with chart ranges and haven't gotten it to work yet.

Please let me know if any further information is needed.

Answer 1

First copy the "good charts" to the any/all sheets based on where you would like them to go.

In the code below replace "Sheet1 (2)" with the sheet name where the original "good charts" were located. Now execute the macro. This will automatically update the formula of all series in each graph in each Worksheet to the name of the Worksheet that they are located in.

Sub UpdateGraphFormulatoActiveWorksheet()

Dim cht As ChartObject
Dim sht As Worksheet
Dim srsFullSeriesCollection As Series
Dim xName As String
Dim Bname As String
Dim CurrentSheet As Worksheet


Application.ScreenUpdating = False
Application.EnableEvents = False


    Set CurrentSheet = ActiveSheet

        For Each sht In ActiveWorkbook.Worksheets
        sht.Activate
        xName = ActiveSheet.Name
            For Each cht In sht.ChartObjects
            cht.Activate
               For Each srsFullSeriesCollection In ActiveChart.FullSeriesCollection()

                    If InStr(1, srsFullSeriesCollection.Formula, "Sheet1 (2)") > 0 Then
                        Bname = srsFullSeriesCollection.Formula
                        Bname = Replace(Bname, "Sheet1 (2)", xName)

                        With srsFullSeriesCollection
                        .Formula = Bname
                        End With

                        End If
                Next srsFullSeriesCollection
           Next cht
        Next sht

CurrentSheet.Activate
Application.EnableEvents = True
     
End Sub