get reference name

demac43 · November 17th, 2006, 07:22 PM

Hi

I,ve given reference names to all column header cells in my sheet "sheet3". there are 40 or so columns.
I have created 3 other sheets (sheets 4,5,6) which are structured the same but as yet have not given reference names to the column header cells.

The names will be similar to those in sheet3 apart from 1 letter change (different for each sheet)at the end of the name. e.g. column A (Row 1) on sheet3 is named "indexD", I want the corresponding name on sheet4 to be "IndexB" and so on. I'm hoping to do it with a macro of course although by the time I'm ready I probably could have done it faster manually. However, it would be useful later on as well to have in the library.

My problem is getting the reference name from sheet3 and putting it in a variable to alter the name and allocate it to the corresponding cell in the other sheet.

I need to know only how to extract the reference name, the method for which seems not as simple as it should be (to me anyway).

should be an easy one for experienced users ..

regards Dennis

maccas · November 20th, 2006, 11:39 AM

Dennis,

Is this the sort of thing you're after?

Code:

Sub Test()

Dim wksSetUp As Worksheet
Dim nmeSetUp As Name

    Set wksSetUp = ThisWorkbook.Sheets("Sheet3")

    For Each rngSetUp In wksSetUp.Names

        MsgBox nmeSetUp.Name & " - " & nmeSetUp.RefersTo

    Next rngSetUp

End Sub

demac43 · November 20th, 2006, 02:35 PM

Not quite maccas, but thanks for trying.

The sheet has named ranges elsewhere as well and i wanted the the code to create the 'name' by parsing the 'name' on the example sheet. e.g.

str3="IndexD"
str4=left(str3,len(str3)-1) & "B" (="IndexB")

It boils down to there being no keyboard shortcut to access the name box and copy the name to the clipboard.

In the end, I did it in conjunction with an external macro program (macro scheduler) which managed the process one name at a time all automated ... attached to the F11 key.

regards Dennis