automatically numbering worksheets

N

Nicole

I know this can be done, I just don't remember how. I hope someone can help me. Here is the situation: I have a workbook with approximatley 30 to 40 worksheets. One worksheet is a template spreadsheet. For the other worksheets I copy the one template into the other worksheets which I number. For example 100, 101, 102,.....and so on. Therefore, the names of the worksheets are 100, 101, 102..... The question is, how do I tell Excel to automatically number these worksheets and also to automatically number a certain cell in each worksheet to correspond to the number name of that particular worksheet. Thank you in advance to anyone that can help me.
 
F

Frank Kabel

Hi
for getting the worksheet name into a cell use
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("file
name",A1),1))

--
Regards
Frank Kabel
Frankfurt, Germany

Nicole said:
I know this can be done, I just don't remember how. I hope someone
Click to expand...
can help me. Here is the situation: I have a workbook with
approximatley 30 to 40 worksheets. One worksheet is a template
spreadsheet. For the other worksheets I copy the one template into the
other worksheets which I number. For example 100, 101, 102,.....and so
on. Therefore, the names of the worksheets are 100, 101, 102..... The
question is, how do I tell Excel to automatically number these
worksheets and also to automatically number a certain cell in each
worksheet to correspond to the number name of that particular
worksheet. Thank you in advance to anyone that can help me.
 
G

Gord Dibben

Nicole

For the auto-naming by number enter 100 down to to 140 in Column A of first
sheet.

Run this macro.

Sub NameWS()
'name sheets with list in A1:A40 on first sheet
On Error Resume Next
For i = 1 To 40
Sheets(i).Name = Sheets(1).Cells(i, 1).Value
Next
End Sub

Gord Dibben Excel MVP
 
Top