Сохранение динамического именованного диапазона на диаграмме при копировании рабочего листа

Вы можете просто повторно указать значения серии. Это очень простой случай с 1 коллекцией серии и 1 диаграммой, где вы копируете лист 1. Существует динамическая серия под названием DynRange, которая уже существует на листе 1. Подложка ниже просто устанавливает серию в скопированной диаграмме обратно в этот диапазон.

Вы можете разработать это, чтобы перебрать все диаграммы на скопированном листе. Возможно, вам потребуется уже зациклить исходные диаграммы и все их серии для хранения (в массиве?) Имен диаграмм, имен / номеров серий и связанных именованных диапазонов, чтобы вы могли правильно применить к новому диапазону.

Или сделайте цикл и установите диаграмму 1 на лист 2, серия 1 = диаграмма 1 на листе 2 серия 1 и т. Д.

Примечание. Вы можете сохранить рабочий лист как официальный Шаблон Excel и используйте его.

Option Explicit

    Sub ResetRange()

        Sheets("Sheet1").Select
        Sheets("Sheet1").Copy Before:=Sheets(1)
        ActiveSheet.ChartObjects("Chart 1").Activate
        ActiveChart.FullSeriesCollection(1).Values = "=Sheet1!DynRange"

    End Sub

Основной код:

А вот грубая и готовая версия того, что я упомянул, чтобы перебрать все диаграммы и все серии, установив эквивалентные динамические диапазоны на листе 1. Обратите внимание, что я тестировал только 1 диаграмму и 2 динамические серии.

Option Explicit

Public Sub ResetRange()

    Dim wb As Workbook
    Dim sourceSheet As Worksheet

    Set wb = ThisWorkbook
    Set sourceSheet = wb.Sheets("Sheet1")
    sourceSheet.Copy Before:=Sheets(1)

    Dim currChart As Long
    Dim currSeries As Series
    Dim thisChart As Chart
    Dim thisSeries As Long

    With ActiveSheet

        For currChart = 1 To .ChartObjects.Count

            Set thisChart = .ChartObjects(currChart).Chart

            For thisSeries = 1 To thisChart.SeriesCollection.Count

                thisChart.SeriesCollection(thisSeries).Formula = sourceSheet.ChartObjects(currChart).Chart.SeriesCollection(thisSeries).Formula

            Next thisSeries

            Set thisChart = Nothing

        Next currChart

    End With

    LoopNamedRanges ActiveSheet

End Sub

Private Sub LoopNamedRanges(ByVal ActiveSheet As Worksheet)

    Dim nm As Name

    For Each nm In ActiveWorkbook.Names

        If nm.RefersToRange.Parent.Name = ActiveSheet.Name Then

            nm.Delete

        End If

    Next nm

End Sub

Данные:

Использованная литература:

• Цикл по всем именованным диапазонам в excel VBA в текущем активном листе
• https://peltiertech.com/dynamic-chart-source-data/
• Excel VBA - получить диапазон данных диаграммы