Wrox Programmer Forums
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old November 17th, 2006, 07:22 PM
Authorized User
Join Date: Nov 2006
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default get reference name


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

Old November 20th, 2006, 11:39 AM
Friend of Wrox
Join Date: Jun 2003
Posts: 173
Thanks: 0
Thanked 3 Times in 3 Posts


Is this the sort of thing you're after?

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
Old November 20th, 2006, 02:35 PM
Authorized User
Join Date: Nov 2006
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts

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.

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

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.