Dear all,

There are 80 sheets in my workbook. I want to name the ranges A1:B20
with systematic names in these 80 sheets, say, student_01 for Sheet1!A1:B20,
student_02 for Sheet2!A1:B20, ..., student_80 for Sheet80!A1:B20. Can I name
these ranges easily? Thanks.

Best Regards,
Andy

Dim i As Long
For i = 1 To 80
Worksheets("Sheet" & i).Range("A1:B20").Name = "student_" &
Format(i, "00")
Next i


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Andy Chan" <chankhandy-(E-Mail Removed)> wrote in message
news:43ad162e$1@127.0.0.1...
> Dear all,
>
> There are 80 sheets in my workbook. I want to name the ranges A1:B20
> with systematic names in these 80 sheets, say, student_01 for
Sheet1!A1:B20,
> student_02 for Sheet2!A1:B20, ..., student_80 for Sheet80!A1:B20. Can I
name
> these ranges easily? Thanks.
>
> Best Regards,
> Andy
>
>

Hi Andy,

Sub InsertNamedSheets()
Dim i As Long
For i = 1 To Worksheets.Count
ActiveWorkbook.Names.Add Name:="Student_" & Format(i, "00"),
RefersToR1C1:= _
"=Sheet" & i & "!R1C1:R20C1"
Next i
End Sub

If you're new to macros, look here first:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

--
Kind regards,

Niek Otten

"Andy Chan" <chankhandy-(E-Mail Removed)> wrote in message
news:43ad162e$1@127.0.0.1...
> Dear all,
>
> There are 80 sheets in my workbook. I want to name the ranges A1:B20
> with systematic names in these 80 sheets, say, student_01 for
> Sheet1!A1:B20, student_02 for Sheet2!A1:B20, ..., student_80 for
> Sheet80!A1:B20. Can I name these ranges easily? Thanks.
>
> Best Regards,
> Andy
>

Thanks, it works!

"Bob Phillips" <(E-Mail Removed)> ¼¶¼g©ó¶l¥ó·s»D:(E-Mail Removed)...
> Dim i As Long
> For i = 1 To 80
> Worksheets("Sheet" & i).Range("A1:B20").Name = "student_" &
> Format(i, "00")
> Next i
>
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Andy Chan" <chankhandy-(E-Mail Removed)> wrote in message
> news:43ad162e$1@127.0.0.1...
>> Dear all,
>>
>> There are 80 sheets in my workbook. I want to name the ranges A1:B20
>> with systematic names in these 80 sheets, say, student_01 for
> Sheet1!A1:B20,
>> student_02 for Sheet2!A1:B20, ..., student_80 for Sheet80!A1:B20. Can I
> name
>> these ranges easily? Thanks.
>>
>> Best Regards,
>> Andy
>>
>>
>
>

Thanks, it works!

"Niek Otten" <(E-Mail Removed)> ¼¶¼g©ó¶l¥ó·s»D:(E-Mail Removed)...
> Hi Andy,
>
> Sub InsertNamedSheets()
> Dim i As Long
> For i = 1 To Worksheets.Count
> ActiveWorkbook.Names.Add Name:="Student_" & Format(i, "00"),
> RefersToR1C1:= _
> "=Sheet" & i & "!R1C1:R20C1"
> Next i
> End Sub
>
> If you're new to macros, look here first:
>
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
> --
> Kind regards,
>
> Niek Otten
>
> "Andy Chan" <chankhandy-(E-Mail Removed)> wrote in message
> news:43ad162e$1@127.0.0.1...
>> Dear all,
>>
>> There are 80 sheets in my workbook. I want to name the ranges A1:B20
>> with systematic names in these 80 sheets, say, student_01 for
>> Sheet1!A1:B20, student_02 for Sheet2!A1:B20, ..., student_80 for
>> Sheet80!A1:B20. Can I name these ranges easily? Thanks.
>>
>> Best Regards,
>> Andy
>>
>
>