VBA copy sheets does not retain named ranges (Excel 2007)

[linuslund]

I have designed a few somewhat complex graphs in Excel 2007 and whish to deploy these as an add-in. I have hence added the sheets with the graphs into my add-in and created a few buttons to copy this sheet into the ActiveWorkbook. The problem I'm encountering is graph is using several named ranges (dynamically sized) and when copying the sheet these named ranges are not kept but converted into the range they are pointing to at the time of copy, hence removing any dynamic behaviour.

I've solved this now by creating a code snippet that recreates the named ranges and relinks the graph formulas, but this is a really slow process. Has anybody been able to copy a sheet using VBA code where graph formulas using named ranges are kept intact?

 

[Jon Peltier]

That problem existed also in Excel 2003 and earlier.

Instead of a sheet within the add-in, save the sheet with the dummy data and chart as a template. Insert a new sheet, based on this template, into the active workbook.

ActiveWorkbook.Worksheets.Add Before:=ActiveSheet, Type:=TemplateFullName

where TemplateFullName is the path and file name of this template.

 

[dplum]

Has anybody been able to copy a sheet using VBA code where ... ranges are kept intact?

I realize that the original posting here was approx 2 years ago.

However, what I have found is timeless
1. if a range name on the source sheet is Global or Scoped to the Workbook then that name is not copied to the new sheet
2. however, if a range name on the source sheet is Local or Scoped to the Worksheet then that name is copied and on the new sheet it becomes local to the new sheet

So the simple solution would seem to be to create the inital set of range names with Local (Worksheet) Scope, and the simple way to do that is to simply (manually) copy the sheet that contains the Global (Workbook scoped) Names and then get rid of that original sheet.

dplum

 

[preddeid]

I realize that the original posting here was approx 2 years ago.

However, what I have found is timeless
1. if a range name on the source sheet is Global or Scoped to the Workbook then that name is not copied to the new sheet
2. however, if a range name on the source sheet is Local or Scoped to the Worksheet then that name is copied and on the new sheet it becomes local to the new sheet

So the simple solution would seem to be to create the inital set of range names with Local (Worksheet) Scope, and the simple way to do that is to simply (manually) copy the sheet that contains the Global (Workbook scoped) Names and then get rid of that original sheet.

dplum

I'm using Excel 2013 and unfortunately didn't find this fix to work.

Using worksheet level defined names that contain dynamic ranges (offset and count).
FYI, here is the defined name for Close =OFFSET(SUN!$G$8,0,0,COUNTA(SUN!$G:$G)-5)

Within my chart I use the defined name as the series to plot eg Series Values =SUN!Close, where SUN is the worksheet name and Close is the defined name.

Chart works great in the SUN worksheet, but when I copy the worksheet (and I'm selecting 'Make a copy'), when I look at the Series Values in the new chart Series Values ='SUN (2)'!$G$8:$G$268

I've lost my dynamic range in the chart BUT when I look at the Name Manager the defined name for Close =OFFSET('SUN (2)'!$G$8,0,0,COUNTA('SUN (2)'!$G:$G)-5), which is correct - still dynamic.

The problem is the chart isn't keeping the defined name, but instead converting it to an actual, non-dynamic range.

I need to copy almost 100 of these worksheets and don't want to manually change the chart series values each time. Any help would be hugely appreciated.

 

[dplum]

Yes, I see the problem:
- The range name does get copied to the new sheet, and can be used independent of the chart.
- However, the Chart's "Data Source" range is converted to a Static Range
And the latter is true even in the original chart -- that is, the chart source does not appear to have the ability to store/use a range name.

So I've created the following macro that is working for me...

Sub Update_Chart()
    Dim oRangeValue As Range, oChart As Chart, vRangeName

    vRangeName = "Close" '<<<-- Assign the dynamic range name used to create the chart
    Application.Goto Reference:=vRangeName
    
    Set oRangeValue = Range(Selection.Address)
    Set oChart = ActiveSheet.ChartObjects(1).Chart
    oChart.SetSourceData Source:=oRangeValue, PlotBy:=xlColumns
    'PlotBy could also be =xlRows
End Sub

Hope this helps!
dplum
Toronto / GTA - Microsoft Excel Training & Consulting, Specializing in Macros & VBA

 

[preddeid]

Thanks for helping dplum.

Unfortunately this isn't working for me . I copied my original worksheet and ran the macro, however, the chart series value is still in the format ='Master (2)'!$G$8:$G$269 instead of ='Master (2)'!Close.

I just copied the macro and ran it. Was I supposed to change it in any way?

BTW, my original worksheet chart does keep the series value in the format ='Master (2)'!Close, it doesn't convert it to a cell reference when I look at it by editing the chart. The copied worksheet is not retaining the defined name.

Thanks

 

[Jon Peltier]

dplum has used a Name to indicate the entire chart source data range, in the SetSourceData command. As soon as the dialog is dismissed, the chart forgets the name of the range and remembers only the address of the range. But this is not the problem you have seen.

When you copy a sheet that contains a chart, the ranges in the chart are preserved, but not the names of those ranges, only the addresses. However, if you move the sheet, the names are preserved.

One way to take advantage of this is to save the sheet as its own workbook, then open this workbook whenever you need a copy of the sheet, and move the sheet into the workbook where you need it. If it's the only sheet in the workbook you'd saved earlier, the workbook is closed without saving, so you don't lose the original sheet.

Another approach is to save the sheet with the chart as a template, then insert a new sheet based on the template.

 

[dplum]

@preddeid,
The idea of using the macro I gave above was not to assign the RangeName to the chart properties, but to simply update the static range in the chart to whatever data range is currently appropriate.

This macro could be executed manually through a shortcut or worksheet button.

Alternatively, you could automate the execution when a change is made to the range in a worksheet that the chart refers to, as follows:

Step 1) In ThisWorkbook enter

Private Sub Workbook_Open()
    Run "Set_Starting_Range"
End Sub

Step 2) In a Standard Module enter:

Option Explicit
Public pLastRangeNameAddress
Public Const pRangeName = "Close" <<-- Set this variable's value to the Dynamic Range Name

Private Sub Set_Starting_Range()
    Dim vRangeNameAddress
    On Error Resume Next
    vRangeNameAddress = Range(pRangeName).Address
    If Err Then
        MsgBox "pRangeName = [" & pRangeName & "] Not Found!" & vbCr & vbCr & _
                "Cannot continue with Open of This Workbook!"
        ActiveWorkbook.Close
    Else
        pLastRangeNameAddress = vRangeNameAddress
    End If
End Sub

Step 3) In the Worksheet that contains the data range and associated chart, enter:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim oRangeValue As Range, oChart As Chart

    If pLastRangeNameAddress <> Range(pRangeName).Address Then
        Set oRangeValue = Range(pRangeName)
        Set oChart = ActiveSheet.ChartObjects(1).Chart
        
        oChart.SetSourceData Source:=oRangeValue, PlotBy:=xlColumns
    '   oChart.SetSourceData Source:=oRangeValue, PlotBy:=xlRows
    
        pLastRangeNameAddress = Range(pRangeName).Address 'Store new range
    End If
End Sub

=========
PLEASE NOTE:
=========
The use of the Public Variable pLastRangeNameAddress works great for a single sheet. However, from what you're describing you'd need an Array of variables (one for each sheet), and the array would need to be ReDimed as new sheets are added... so you'd still have some work to do here.

dplum
http://excelsmart

 

[Jaddi01]

I've got exactly the same issue.

If the charts on my sheet is not VBA generated, how can you then force the data range back to the named range?

I see they are still listed in my copied workbook and each named range is allocated to each individual sheet and graph, not the entire workbook...still no go!