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?
 
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
 
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
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
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
 
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
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.