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