Moving a local name to another sheet

[Paul Martin]

In code, I'm rebuilding various sheets in a workbook by cutting and
pasting the contents of a sheet to a new one, but the local names on a
sheet remain attached to the old sheet (which I want to delete). Is
there a way to make the name attach to the new sheet without looping
through all the names and find the local names?

Thanks in advance

Paul Martin
Melbourne Australia

 

[Joel]

Use PasteSpecial and paste the values

Sheets("Sheet1").Range("A1:B3").copy
Sheets("Sheet2").PasteSpecial _
Paste:=xlPasteValues

 

[Paul Martin]

Thanks for the response Joel, but I can't see how that will copy the
local range name from Sheet1 to Sheet2, which is what I'm after.

 

[Paul Martin]

FWIW, this is the solution I've written, until or unless I can find
something more elegant:

Private Sub CopyLocalNames(ByRef wsOld As Worksheet, _
ByRef wsNew As Worksheet)
Dim nam As Name
Dim strName As String
Dim intLen As Integer
Dim intPosn As Integer
Dim strRefersTo As String

For Each nam In wsOld.Names
With nam
intLen = Len(.Name)
intPosn = InStr(1, .Name, "!") + 1
strName = Mid(.Name, intPosn, intLen)
strRefersTo = Replace(nam.RefersTo, _
wsOld.Name, wsNew.Name)
End With

wsNew.Names.Add wsNew.Name & "!" & strName, strRefersTo
Next nam
End Sub

 

[Patrick Molloy]

I'm not 100% with you on this. Is you've selected a named range, when you CUT
then PASTE then range, with its name also gets copied.

an alternative would be, in code, to select the range save the name, delete
the name from the sheet, paste and name the data onto the new sheet using the
saved name

 

[Bony Pony]

I use a similar method to your code Paul. I don't like to cut unless I know
the full impact on a subsequent dependent range. Your way is safer. Longer
but safer - imho of course ....

 

[Paul Martin]

Patrick and Bony Pony, I have a particular reason for cut and paste
(basically to refresh the sheets, to clear Excel's internal cache,
resulting in a significant reduction in file size - from 40MB to
15MB).

The problem is that when a the contents of a worksheet are cut and
pasted to a new worksheet, the name still belongs to the old sheet
while its RefersTo property belongs to the new sheet. So deleting the
old sheet leaves the name with a #REF error.

My code addresses this problem, but I've detected another problem
which is similar: A global name that has a relative address (relative
column OR relative row OR both) behaves like a local name with cut and
paste. My code above doesn't work with these, because they are not
local names. I'll have a go at this.