Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving a local name to another sheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving a local name to another sheet
Use PasteSpecial and paste the values
Sheets("Sheet1").Range("A1:B3").copy Sheets("Sheet2").PasteSpecial _ Paste:=xlPasteValues "Paul Martin" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving a local name to another sheet
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving a local name to another sheet
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving a local name to another sheet
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 .... -- "There are 10 types of people in this world. Those who understand Binary and those who don''t ..." "Paul Martin" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving a local name to another sheet
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving a local name to another sheet
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 "Paul Martin" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula for moving information from one sheet to another sheet | Excel Discussion (Misc queries) | |||
5 man can work simultaneous on a Sheet saved on a local server? | Excel Discussion (Misc queries) | |||
COUNTIF( {local cells}, {number from other sheet}) | Excel Worksheet Functions | |||
moving informatiion from one sheet to a total sheet | Excel Discussion (Misc queries) | |||
Reference styles and local/non-local formulae - international problems. | Excel Programming |