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!

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

"Stephen" <(E-Mail Removed)> wrote in message
news:97BE9CF2-44F6-411E-B342-(E-Mail Removed)...
> 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!

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
"Stephen" <(E-Mail Removed)> wrote in message
news:97BE9CF2-44F6-411E-B342-(E-Mail Removed)...
> 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!

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" wrote:

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

that's sweet!

thanks a bunch!

"Peter T" wrote:

> 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
> "Stephen" <(E-Mail Removed)> wrote in message
> news:97BE9CF2-44F6-411E-B342-(E-Mail Removed)...
> > 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!
>
>
>