asked on

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

zorvek (Kevin Jones)

Do it this way:

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

Kevin

Rory Archibald

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

Open in new window

will do it.

hindersaliva

ASKER

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.

zorvek (Kevin Jones)

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

zorvek (Kevin Jones)

Yikes! I was wrong!

Kevin

ASKER CERTIFIED SOLUTION

zorvek (Kevin Jones)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Rory Archibald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

hindersaliva

ASKER

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/