Forum Discussion

AmyM-NirR's avatar
AmyM-NirR
Copper Contributor
Jan 17, 2022

Copying sheet with dynamic chart

Excel 365

Suppose I use the method of defining dynamic arrays as a named range, and then use that named range in a chart (thus allowing it to expand or shrink to the true size of the dynamic array)

If I try to copy the sheet, Excel will not create a new named range and implement it into the chart data.

Instead, it uses the usual cell referencing of the current non-empty cells in the dynamic range, thus preventing the chart from being dynamic and also displaying an excel error. Is there a workaround for this issue, which doesn't involve the use of VBA? Thanks!

excel

1 Reply

  • Ihabmalaeb's avatar
    Ihabmalaeb
    Copper Contributor
    Feb 07, 2022

    Dear,

     

    I was facing the same issue. Unfortunately, the only way is using macros and I created the following code and it worked well:

    With ActiveSheet
    With ActiveSheet.ChartObjects(1).chart

    Set srsNew = .SeriesCollection.NewSeries
    With srsNew

    .Values = ("'" & ActiveSheet.Name & "'" & "!YDATA")
    .XValues = ("'" & ActiveSheet.Name & "'" & "!XDATA")
    'YDATA and XDATA are the names of your named range
    End With

    End With

    End With

Resources