Numbering worksheets

I am using multiple worksheets within a workbook. In
cell A1 of each sheet is the number representing the
position of the worksheet (i.e. the first worksheet
has '1.' in cell A1, the second worksheet has '2.' in
cell A1). Is there a way for Excel to automatically fill
in this number?

I have used [='sheet1'A1+1] however if I add a sheet
between two existing sheets then the formula is broken
(i.e. adding a new worksheet between the existing 15th
and 16th worksheets, the 16th sheet becomes the 17th but
cell A1 will still point to the 15th sheet.)

Does this help?

Private Sub Workbook_NewSheet(ByVal Sh As Object)
For i = 1 To Sheets.Count
Sheets(i).Range("A1") = i
Next
End Sub


Geoff



"Shelly" wrote in message
...
I am using multiple worksheets within a workbook. In
cell A1 of each sheet is the number representing the
position of the worksheet (i.e. the first worksheet
has '1.' in cell A1, the second worksheet has '2.' in
cell A1). Is there a way for Excel to automatically fill
in this number?

I have used [='sheet1'A1+1] however if I add a sheet
between two existing sheets then the formula is broken
(i.e. adding a new worksheet between the existing 15th
and 16th worksheets, the 16th sheet becomes the 17th but
cell A1 will still point to the 15th sheet.)

Does this help?

Private Sub Workbook_NewSheet(ByVal Sh As Object)
For i = 1 To Sheets.Count
Sheets(i).Range("A1") = i
Next
End Sub


Geoff


"Shelly" wrote in message
...
I am using multiple worksheets within a workbook. In
cell A1 of each sheet is the number representing the
position of the worksheet (i.e. the first worksheet
has '1.' in cell A1, the second worksheet has '2.' in
cell A1). Is there a way for Excel to automatically fill
in this number?

I have used [='sheet1'A1+1] however if I add a sheet
between two existing sheets then the formula is broken
(i.e. adding a new worksheet between the existing 15th
and 16th worksheets, the 16th sheet becomes the 17th but
cell A1 will still point to the 15th sheet.)

Does this help?

Private Sub Workbook_NewSheet(ByVal Sh As Object)
For i = 1 To Sheets.Count
Sheets(i).Range("A1") = i
Next
End Sub

Geoff

NB: I already posted this reply on 3 October, but it did not appear in my
news-feed, so I am not sure what happened. Apologies to anyone who has
received this post more than once.


"Shelly" wrote in message
...
I am using multiple worksheets within a workbook. In
cell A1 of each sheet is the number representing the
position of the worksheet (i.e. the first worksheet
has '1.' in cell A1, the second worksheet has '2.' in
cell A1). Is there a way for Excel to automatically fill
in this number?

I have used [='sheet1'A1+1] however if I add a sheet
between two existing sheets then the formula is broken
(i.e. adding a new worksheet between the existing 15th
and 16th worksheets, the 16th sheet becomes the 17th but
cell A1 will still point to the 15th sheet.)