PDA

View Full Version : Copy and rename named cells in a sheet to another sheet



rstuart
09-30-2011, 01:54 AM
Is there some simple VBA code that will allow me to:

copy the 'names' in the name manager referring to cells in worksheet one and offset those names with a text prefix (i.e. tab2'cellname') and apply these new names to identical ranges in worksheet two?

Any help appreciated.

Bob Phillips
09-30-2011, 02:31 AM
Dim nme As Name

Set nme = ActiveWorkbook.Names("cellName")
ActiveWorkbook.Names.Add Name:="tab2" & nme.Name, _
RefersTo:="=Sheet2!" & Right$(nme.RefersTo, Len(nme.RefersTo) - InStr(nme.RefersTo, "!"))

rstuart
09-30-2011, 05:40 AM
as a very novice user of VBA (almost none) - how would I apply this code in the workbook I wish to copy named cells inside of?

What parts of the code do I input values to so it works?

Thanks :-)

Bob Phillips
09-30-2011, 10:40 AM
Just create a simple macro, changes the strings to your values and run it.