VBA code that creates a series of multiple like-named local ranges

I have a series of 30 worksheets with names like "Wk 1", Wk 2", etc. up to "Wk 30". I have a global range named "ETD_Avail", defined as "='Wk 9'!$F$25:$F$41" I would like to duplicate the range as a local range on all 30 sheets. How would I do that with VBA?

The code below loops through the sheets but ends up with a global name referring to the last sheet. How do i write it so that each sheet gets its own local name?

Thanks,
John
Sub LocalNames()
For i = Worksheets("Wk 7").Index To Worksheets.Count
    Worksheets(i).Select
[k1] = ActiveSheet.Name
    Dim str As String
    str = "='" & ActiveSheet.Name & "'!R25C9:R40C9"
    ActiveWorkbook.Names.Add Name:="ETD_Avail2", RefersToR1C1:=str
    Range("ETD_Avail2").Select
 Next
End Sub

Open in new window

LVL 1
John CarneyReliability Business Tools Analyst IIAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

StephenJRCommented:
Line 7 of your code appears to give the same name "ETD_Avail2" to each of the named ranges - presumably this is not what you want.
0
John CarneyReliability Business Tools Analyst IIAuthor Commented:
No I want each sheet to have its own local version of the name:

'Wk 1!$F$25:$F$41
'Wk 2"!$F$25:$F$41
'Wk 3!$F$25:$F$41
.......

'Wk 30"!$F$25:$F$41

0
StephenJRCommented:
But do you see that line 7 gives every range the same name? Have you tried calling it something like

Name:="ETD_Avail" & i

?
0
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

McOzCommented:
You need to change your code to this: (use ActiveSheet.Names.Add instead of ActiveWorkbook.Names.Add)
Sub LocalNames()
For i = Worksheets("Wk 7").Index To Worksheets.Count
    Worksheets(i).Select
[k1] = ActiveSheet.Name
    Dim str As String
    str = "='" & ActiveSheet.Name & "'!R25C9:R40C9"
    ActiveSheet.Names.Add Name:="ETD_Avail2", RefersToR1C1:=str
    Range("ETD_Avail2").Select
 Next
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
John CarneyReliability Business Tools Analyst IIAuthor Commented:
I'm sorry, that wasn't very clear. What I want to end up with is 30 local names defined like this:

Wk 1'!ETD_Avail2  = 'Wk 1!$F$25:$F$41
Wk 2'!ETD_Avail2  = 'Wk 2!$F$25:$F$41
Wk 3'!ETD_Avail2  = 'Wk 3!$F$25:$F$41

Thanks,
John


0
McOzCommented:
OK, use this:
Sub LocalNames()
For i = Worksheets("Wk 7").Index To Worksheets.Count
    Worksheets(i).Select
[k1] = ActiveSheet.Name
    Dim str As String
    str = "='" & ActiveSheet.Name & "'!F25:F41"
    ActiveSheet.Names.Add Name:="ETD_Avail2", RefersToR1C1:=str
    Range("ETD_Avail2").Select
 Next
End Sub

Open in new window


The actual name itself can't be 'Wk 1'!ETD_Avail2 since it contains illegal charactrers. But this code makes a locally scoped version of the same name 'ETD_Avail2' for every sheet, so you can reference the one from another sheet by using 'Wk 1'!ETD_Avail2

Hope that makes sense.
0
John CarneyReliability Business Tools Analyst IIAuthor Commented:
Excellent, I just learned something new ... which of course is the whole point :-)

Thanks, McOz

- John
0
McOzCommented:
No problem at all!
happy coding
-Oz
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.