Copy sheet - issue with named ranges

[svjensen]

I need to copy sheets from one workbook to an other using VBA.
But I am having some trouble with named ranges.

Both the source file and the detination file have identical data sheets which contains various data - including a large number of named ranges.
These are used for lookup functions, data validation etc. on the other sheets.

I now need to copy these other sheets, but when doing so the named ranges in the destination file are changed, so that the ones which are used by the copied sheet now links to the source file.

I suppose that I could define the names again when I am done copying, but is there an easier way?

/Soren

 

[sijpie]

Three thoughts:

Have you looked into the possibility of using sheet dependant named ranges (define them without sheet name)?

Also, are the other sheets supposed to use the range names of the copied sheet and original sheet or only of the original sheet? Else you could delete the range names before you copy the sheet.

Why do the range names need to be the same. Can you not give the origninal sheet different range names to avoid the duplication issue?

 

[svjensen]

I might not have been clear enough in my description, so let me give it an other go.

As mentioned both files have a data sheet which contains common data (i.e. department names). Most of these are defined using named ranges and are used for data validation etc. on the other sheets.
Since the data can change over time I need a central list.

In other words, the named ranges all link to the central data shet, but since an identical sheet (with identical named ranges) exist in the source file, the references in the destination file are changed when I copy a sheeet that uses the names. So instead of having a reference like "='Data Sheet'!$A$1:$B$5" I get "=[SourceFile.xls]'Data Sheet'!$A$1:$B$5"

 

[sijpie]

OK, so you want to break the links of the copie sheet with the source sheet and set these links to the current (destination) sheet.

Add this code to your code somewhere after the sheet has been copied:

dim aLinks as Variant
 
         aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
         If Not IsEmpty(aLinks) Then
                   For i = 1 To UBound(aLinks)
                            ActiveWorkbook.ChangeLink Name:=aLinks(i) _
                                , Newname:=CurrentWorkBook.Name, Type:=xlExcelLinks
                   Next i
         End If

 

[svjensen]

I assume that 'CurrentWorkbook' is the destination workbook (where I need to update the named ranges)(?)

Anyway, it did not do the trick
And I think mayby I know why.
If I understand the script correctly, it changes all external links to internal links. So that if a given cell has the formula "=[File1.xls]Sheet1!A1" it will be changed to "=Sheet1!A1".

Now the thing is, that the fomulas in the cells are correct, as they used the named range, but it is reference of the named range, which needs to be updated.
Let us say that cell A1 in Sheet1 has the formula "=B1*TaxRate", then this should stay the same.
But if I go to 'Define Name' (CTRL+F3) the reference for 'TaxRate' is "='[SourceFile.xls]Data Sheet'!C3" where it should be "='Data Sheet'!C3".

In other words it is the definition of the named ranges, which should be updated.

Is your script suppose to do that?

 

[svjensen]

If everything else fails I suppose that I could do the following:

Prior to copying the sheet I insert a list of all named ranges in a temporary sheet ('Insert > Name > Paste - Paste list'), and then I am done copying I loop through these and add them again. That should overwrite the existing ones if I understand it correctly.

 

[svjensen]

I am trying to work along the lines of my last post, and in general it seems to work out fine.
But there are two issues.

1. Some of the named range niclude functions such as 'OFFSET'. Since I am running the Danish version of Excel 2003, the list which is generated when I choose 'Insert > Name > Paste - Paste List' includes the Danish version of the function (OFFSET = FORSKYDNING), which VBA apparently will not accept.
Is there a way around this?

2. Apparently what happens when the sheet is copied is that a sheet level named range is created. So mayby it is enough to delete those.
Is there a way to delete sheet level names only?

/Soren