get reference name

demac43 · #1 (**permalink**) 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 · #2 (**permalink**) 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 · #3 (**permalink**) 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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
By value, By reference	watashi	C++ Programming	1	October 10th, 2007 11:04 PM
reference.	scandalous	ASP.NET 2.0 Basics	1	April 9th, 2007 07:29 PM
in need of reference!	alialibidad	SQL Language	1	June 4th, 2006 05:14 AM
in need of reference!	alialibidad	ASP.NET 2.0 Professional	0	June 3rd, 2006 04:11 PM
reference is not valid	paul20091968	Excel VBA	0	May 19th, 2006 04:55 AM