При использовании таблицы шаблона ссылка на именованный диапазон не изменяется в диаграммах.

Question

Я создал шаблон листа с несколькими диаграммами, которые используют динамические именованные диапазоны для обновления при добавлении новых данных. Эти именованные диапазоны ограничены собственной таблицей, а не областью действия книги. Я намерен использовать шаблон для создания рабочей книги со многими вкладками, которые должны ссылаться на свои собственные именованные диапазоны. Я сохранил этот шаблон в папке шаблонов Excel по умолчанию, чтобы я мог вставить шаблон, щелкнув правой кнопкой мыши вкладку, выбрав "Вставить" и выбрав его из списка.

После того, как я вставил лист шаблона и переименовал его из "Шаблон" в "Продукт 1", некоторые ссылки на диаграммы не обновляются и продолжают ссылаться на именованные диапазоны, используя имя рабочего листа "Шаблон", а не обновляют их до "Продукт 1". Имя листа. Когда я выбираю данные в диаграммах, которые не обновляются, =SERIES то, что обычно отображается в панели функций, отсутствует, и я не могу редактировать панель функций. Когда я редактирую данные в диаграмме, именованные диапазоны все еще ссылаются на имя листа "Шаблон" (например, =Template!Gross_Margin вместо ='Product 1'!Gross_Margin). Если я оставлю ссылку как "Шаблон" и нажму Enter, Excel скажет, что это недопустимая ссылка. Когда я проверяю Менеджер имен, нет именованных диапазонов, которые ссылаются на лист "Шаблон".

Я искал макрос для решения проблемы и заменил все ссылки на имя старого листа новым именем листа и нашел следующий код:

Sub ChangeSeriesFormulaAllCharts()
    ''' Do all charts in sheet
    Dim oChart As ChartObject
    Dim OldString As String, NewString As String
    Dim mySrs As Series
    Dim iChartType As XlChartType
    Dim sFormula As String

    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 oChart In ActiveSheet.ChartObjects
            For Each mySrs In oChart.Chart.SeriesCollection
                sFormula = ""
                On Error Resume Next
                sFormula = mySrs.Formula
                On Error GoTo 0
                ' change to column chart if series is inaccessible
                If Len(sFormula) = 0 Then
                    iChartType = mySrs.ChartType
                    mySrs.ChartType = xlColumnClustered
                End If
                mySrs.Formula = WorksheetFunction.Substitute(mySrs.Formula, OldString, NewString)
                If Len(sFormula) = 0 Then mySrs.ChartType = iChartType
            Next
        Next
    Else
        MsgBox "Nothing to be replaced.", vbInformation, "Nothing Entered"
    End If
End Sub

Проблема с этим кодом в том, что он использует =SERIES функциональность, которую, как я уже упоминал выше, я не могу редактировать. Когда я запустил приведенный выше код, он вернул сообщение об ошибке.

Эта проблема полностью лишает смысла использование шаблонов, поскольку я пытаюсь избежать обновления ссылок на диаграммы по одному. В итоге у меня будет>50 листов в этой книге и ~10 диаграмм в каждой, так что выполнение этого вручную займет слишком много времени.

Сначала я подумал, что это произошло из-за того, что я случайно сделал при создании шаблона, поэтому я переделал всю вещь с новым рабочим листом, но те же самые серии в тех же самых диаграммах не обновляют имя рабочего листа. Диаграммы представляют собой диаграммы рассеяния, гистограммы, линейные диаграммы и комбинированные диаграммы.

Что-то мне не хватает в настройке или это присуще только шаблонам? Если последнее, есть ли способ с помощью макросов заменить все ссылки на старое имя рабочего листа новым именем рабочего листа?