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?
For i = Worksheets("Wk 7").Index To Worksheets.Count
[k1] = ActiveSheet.Name
Dim str As String
str = "='" & ActiveSheet.Name & "'!R25C9:R40C9"
ActiveWorkbook.Names.Add Name:="ETD_Avail2", RefersToR1C1:=str