automatic new serial number for each new sheet within one file

[ahmed]

i want to creat an automatic serial nos. when i`m creating an new sheet
inside the current workbook;
Example : inside sheet no.(1) appears a serial noumber (001)
and by creating a new sheet in the same workbook appears in the sheet no. (2)
the serial no. (002).... etc.

Thank You very much

[JE McGimpsey]

One way:

Put this in your ThisWorkbook code module (if you're unfamiliar with
macros, see

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

for help).

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Dim ws As Worksheet
Dim nSerialNumber As Long
If TypeName(Sh) = "Worksheet" Then
For Each ws In Worksheets
With ws.Range("A1")
If .Value > nSerialNumber Then nSerialNumber = .Value
End With
Next ws
With Sh.Range("A1")
.Value = nSerialNumber + 1
.NumberFormat = "000"
End With
End If
End Sub


In article <38717281-55FA-4551-BE50-281AA8F2E7C9@microsoft.com>,
"ahmed" <ahmed@discussions.microsoft.com> wrote:

> i want to creat an automatic serial nos. when i`m creating an new sheet
> inside the current workbook;
> Example : inside sheet no.(1) appears a serial noumber (001)
> and by creating a new sheet in the same workbook appears in the sheet no. (2)
> the serial no. (002).... etc.
>
> Thank You very much