Automating creation of named ranges???

[Stephen]

Hi Folks,

I'm have 52 sheets named 1,2,3,etc., that I want to create named ranges on
in the following fashion...

sheet 1 (named "1") has A:C named w1s
sheet 1 (named "1") has D:F named w1c

sheet 2 (named "2") has A:C named w2s
sheet 2 (named "2") has D:F named w2c

.... all the way down the line.

Is there a simple way to slap together a one-time use macro so I don't have
t odo this all manually?

Thanks in Advance!

 

[Zone]

How about this? James

Sub NameEm()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Activate
Columns("A:C").Name = "w" & ws.Name & "s"
Columns("D:F").Name = "w" & ws.Name & "c"
Next ws
End Sub

 

[Peter T]

Sub Namer()
Dim b As Boolean
Dim ws As Worksheet
Dim nms As Names
Set nms = ActiveWorkbook.Names
For Each ws In ActiveWorkbook.Worksheets
With ws
b = False
On Error Resume Next
b = Val(.Name) = .Name
On Error GoTo 0
If b Then
nms.Add "w" & .Name & "s", .Range("A:C")
nms.Add "w" & .Name & "c", .Range("D:F")
End If
End With
Next

End Sub

If you were to use Worksheet level names, also known as Local names, each
sheet could have identically named Names, eg "ws" & "wc"

Regards,
Peter T

 

[Joel]

Sub nameranges()
For sheetcount = 1 To 52
refername = "=" & Chr(39) & "names " & sheetcount & Chr(39) & "!A:C"
ActiveWorkbook.Names.Add Name:="w" & sheetcount & "s", _
RefersTo:=refername
refername = "=" & Chr(39) & "names " & sheetcount & Chr(39) & "!D:F"
ActiveWorkbook.Names.Add Name:="w" & sheetcount & "c", _
RefersTo:=refername
Next sheetcount
End Sub

 

[Stephen]

that's sweet!

thanks a bunch!

Sub Namer()
Dim b As Boolean
Dim ws As Worksheet
Dim nms As Names
Set nms = ActiveWorkbook.Names
For Each ws In ActiveWorkbook.Worksheets
With ws
b = False
On Error Resume Next
b = Val(.Name) = .Name
On Error GoTo 0
If b Then
nms.Add "w" & .Name & "s", .Range("A:C")
nms.Add "w" & .Name & "c", .Range("D:F")
End If
End With
Next

End Sub

If you were to use Worksheet level names, also known as Local names, each
sheet could have identically named Names, eg "ws" & "wc"

Regards,
Peter T