same range name in different sheets

[Gilbert DE CEULAER]

I have several sheets in the same workbook (e.g. year1, year2, year3,...).
I would like to name the same range in every sheet by the same name (e.g.
salesmonth1)
Is there a way to do this "in one move", I mean without going from sheet to
sheet.
Thanks in advance
Gilbert

 

[JP]

If you were interested in doing this programmatically, you could do
something like this:

Dim i As Long
For i = 1 to Worksheets.Count
Worksheets(i).Names.Add Name:="salesmonth1", RefersToR1C1:= "=" &
Worksheets(i).Name & "!R5C2:R18C5"
Next i

In the end, cells B5:E18 of each worksheet would be named
"salesmonth1"

--JP

 

[Dave Peterson]

On more:

Option Explicit
Sub testme()

Dim iCtr As Long
Dim wks As Worksheet

iCtr = 0
Do
iCtr = iCtr + 1

Set wks = Nothing
On Error Resume Next
Set wks = Worksheets("year" & iCtr)
On Error GoTo 0

If wks Is Nothing Then
Exit Do
End If

wks.Range("A1:b99").Name = "'" & wks.Name & "'!salesmonth1"
Loop

End Sub