Batch Naming of ranges

[Andy Chan]

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

 

[Bob Phillips]

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)

 

[Niek Otten]

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

 

[Andy Chan]

Thanks, it works!

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]

Thanks, it works!

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