On 2002-04-14 07:40, daleyman wrote:
Private Sub Workbook_NewSheet(ByVal Sh As Object)
maxval = 0
For sn = 1 To ActiveWorkbook.Sheets.Count
If Sheets(sn).Name<> Sh.Name Then
If IsNumeric(Sheets(sn).Range("e3")) And Sheets(sn).Range("e3") > maxval Then maxval = Sheets(sn).Range("e3").Value
If IsNumeric(Sheets(sn).Range("e4")) And Sheets(sn).Range("e4") > maxval Then maxval = Sheets(sn).Range("e4").Value
End If
Next
Sh.Range("e3") = maxval + 1
Sh.Range("e4") = maxval + 2
End Sub
...is all you need, take away the extra "Sub AddNewNumber"...the statement:
Private Sub Workbook_NewSheet(ByVal Sh As Object)
...opens the procedure, you need to paste the block of code into the<u>module that relates to the Workbook</u>. NewSheet is the event, therefore, whenever you add a new sheet this macro will run automatically, because excel will look in the Workbook module to see if there is such a procedure.
If you don't know where the Workbook module is, go into the Visual Basic Editor and double click on the "ThisWorkbook" label, this will open up the code sheet for that object.
Good Luck....
_________________<table style="background-color:#0e54be" cellspacing="1" cellpadding="2"><td style="background-color:#ceffff;font-family:arial;color:#072c63;font-size:8pt;"> DALEY :P</td></table>