automatic sequential numbering

[eflaten]

I try to get a visual basis script on
http://www.mcgimpsey.com/excel/udfs/sequentialnums.html

to work on new pages based on a template located in:
Macintosh HD:Applications: Microsoft Office 2004:Templates:My
Templates

I paste the scripte under doing the following. I click on ThisWorkbook
and paste the script with two dropdows left as they appear (General)
and (Declarations).

"run time error 9
Subscript out of range"

Debug shows a yellow arrow on the line number 8:
With ThisWorkbook.Sheets("Invoice")

The filename of the template is "Invoice.xlt"

Here is the script. Its the first one the webpage I refer to above.
Thanks for any help



Private Sub Workbook_Open()
Const sAPPLICATION As String = "Excel"
Const sSECTION As String = "Invoice"
Const sKEY As String = "Invoice_key"
Const nDEFAULT As Long = 1&
Dim nNumber As Long

With ThisWorkbook.Sheets("Invoice")
With .Range("B1")
If IsEmpty(.Value) Then
.Value = Date
.NumberFormat = "dd mmm yyyy"
End If
End With
With .Range("B2")
If IsEmpty(.Value) Then
nNumber = GetSetting(sAPPLICATION, sSECTION, sKEY,
nDEFAULT)
.NumberFormat = "@"
.Value = Format(nNumber, "0000")
SaveSetting sAPPLICATION, sSECTION, sKEY, nNumber
+ 1&
End If
End With
End With
End Sub

 

[JE McGimpsey]

I try to get a visual basis script on
http://www.mcgimpsey.com/excel/udfs/sequentialnums.html

to work on new pages based on a template located in:
Macintosh HD:Applications: Microsoft Office 2004:Templates:My
Templates

I paste the scripte under doing the following. I click on ThisWorkbook
and paste the script with two dropdows left as they appear (General)
and (Declarations).

"run time error 9
Subscript out of range"

Debug shows a yellow arrow on the line number 8:
With ThisWorkbook.Sheets("Invoice")

The error indicates that you do not have a worksheet named "Invoice" in
your template workbook.

You can either rename the worksheet, or you can replace "Invoice" in the
line above with the name of your target worksheet.