• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 288
  • Last Modified:

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


0
hindersaliva
Asked:
hindersaliva
  • 4
  • 2
  • 2
2 Solutions
 
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
 
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
zorvek (Kevin Jones)ConsultantCommented:
This works:

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

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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now