How can I automatically add the sheet name to a defined name when copying the 'master sheet'

[AGill208]

The 'master sheet' in the attached file works when the number of piles is changed as the chart correctly changes. The sheet has defined names to determine the length of the chart series to be used for 'piles' and 'pile cap' as these vary.

However, when the sheet is copied (and many copies may be needed for a project) the defined name stays the same and this seems to cause a problem when determining the length of the two varying chart series.

I assume there is a solution to this problem, but my knowledge of Excel is limited and all assistance would be gratefully received.

Regards

Anthony Gill

Excel version 2012 (build 13801.20360)

[#2 · Accepted solution]

@AGill208 This is fixed easy enough using a small macro. After copying the tab, activate that worksheet and run this macro:

Sub FixSeriesFormulas()
    Dim Cht As Chart
    Dim ser As Series
    Set Cht = ActiveSheet.ChartObjects(1).Chart
    For Each ser In Cht.SeriesCollection
        Select Case ser.Name
        Case "Piles"
            ser.Formula = "=SERIES(""Piles"",'" & ActiveSheet.Name & "'!DATA_PX,'" & ActiveSheet.Name & "'!DATA_PZ,1)"
        Case "Pile Cap"
            ser.Formula = "=SERIES(""Pile Cap"",'" & ActiveSheet.Name & "'!DATA_CX,'" & ActiveSheet.Name & "'!DATA_CZ,5)"
        End Select
        
    Next
End Sub

[#3]

I need someone help to avoid errors with large files calculations

[#4]

@Maru_Martiarena I advise you to start  new thread in the forum by clicking the "Start a Discussion" button.

[#5]

Thanks you!

[#6]

Thanks Jan - works a treat.