Named Ranges

[James Hamilton]

Hello,

I have four worksheets that have labels in exactly the same cells as each
other.

What I want to do is create named ranges in one worksheet, and then copy
them over to the other worksheets so that I only have to change the name of
the sheet that the range is referring to (as the cells are the same).

I know how to make named ranges in one worksheet....I'm looking for a quick
way to copy those named ranges to the other worksheets.

I hope this makes sense - thanks.

 

[Dave Peterson]

How about a macro?

If yes, group all the sheets you need.
(click on the first worksheet tab and ctrl-click on subsequent)

Then run this macro:

Option Explicit
Sub testme()

Dim myAddr As Variant
Dim myNames As Variant
Dim wks As Worksheet
Dim iCtr As Long

myAddr = Array("a1", "b3:c99", "D", "A:E")
myNames = Array("Name1", "Name2", "Name3", "Name4")

If UBound(myAddr) <> UBound(myNames) Then
MsgBox "design error"
Exit Sub
End If

For Each wks In ActiveWindow.SelectedSheets
With wks
For iCtr = LBound(myAddr) To UBound(myAddr)
.Range(myAddr(iCtr)).Name = "'" & .Name & "'!" & myNames(iCtr)
Next iCtr
End With
Next wks

End Sub

And when you're done, ungroup those sheets!

This create worksheet level names for each of the selected sheets.