Excel VBA - referring to sheet

I'm adding a common Named Range to every sheet, looping through all sheets. I want to apply the following.

    Dim wsSheet As Worksheet
    Set wsSheet = ActiveSheet
    wsSheet.Names.Add Name:="NewName2", RefersTo:="='Sheet1'!$D$5"

The reference to the Sheet1 need to change on each sheet. I can see this will be a pain if I construct a string from the sheet name. Is there a more elegant way to refer to wsSheet.Name in the RefersTo ?
Or is strThisSheet = "='" + wsSheet.Name + "'!$D$5" the only way?
Thanks


hindersalivaAsked:
Who is Participating?
 
zorvek (Kevin Jones)ConsultantCommented:
This works:

    Dim wsSheet As Worksheet
    Set wsSheet = ActiveSheet
    wsSheet.Range("D5").Name = "'" & wsSheet.Name & "'!NewName2"

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
Do it this way:

    Dim wsSheet As Worksheet
    Set wsSheet = ActiveSheet
    wsSheet.Range("D5").Name ="NewName2"

Kevin
0
 
Rory ArchibaldCommented:
wsSheet.Names.Add Name:="NewName2", RefersTo:="='" & wsSheet.Name & "'!$D$5"

Open in new window

will do it.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
hindersalivaAuthor Commented:
Yes Rory, I was just being lazy to try that. But I had already written the code in order to ask the question (LOL!) so tried it and it worked.

Kevin's code was what I was looking for BUT I see that it does not set the Scope to Worksheet. It's got a Workbook scope. Any ideas on how to force that?

Thanks chaps.
0
 
zorvek (Kevin Jones)ConsultantCommented:
You don't need to force a worksheet scope. As soon as you define the same name on second worksheet Excel automatically creates worksheet scope names.

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
Yikes! I was wrong!

Kevin
0
 
Rory ArchibaldCommented:
I don't follow - in what way does what I suggested not do what you want?

You could also use:
wsSheet.Range("D5").Name ="'" & wsSheet.Name & "'!NewName2"

Open in new window

0
 
hindersalivaAuthor Commented:
Rory, what you suggest does do what I want. I was only trying to (rather unnecessarily) avoid constructing a Formula for the RefersTo.
You have both been very helpful, as usual :)
Thanks/
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.