Creating Sequential Numbering Macro for an Quotation template

[steewwy]

I am creating a quotation template for the company I work for. I want
to put in a sequential number every time the document is saved so that
the client has a unique reference number.

I have seen various sequential numbering scripts but cant get them to
run, they always require debugging.

I am a newcomer to running macros so a very simplistic reply would be
appreciated.

Many Thanks
Stewwy

 

[Otto Moehrbach]

Put this macro in the workbook module:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
With Sheets("TheSheetName")
.Range("A1") = .Range("A1") + 1
End With
End Sub

This macro increments the number in A1 of a sheet named "TheSheetName" every
time the workbook is saved.
To locate the workbook module, open Excel, open your file, right-click on
the Excel icon found to the left of the word "File" in the menu across the
top of the screen, select View Code, paste the macro into the displayed
module. HTH Otto

 

[Bob Phillips]

Stewwy,

Here is one way

Private Const kBaseName As String = "myFile"
Private Const kName As String = "__RefNum__"

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim iPos As Long
On Error GoTo CleanUp
Application.EnableEvents = False

If IsError(Evaluate(kName)) Then
Me.Names.Add Name:=kName, RefersTo:=1
Else
Me.Names.Add Name:=kName, RefersTo:=Evaluate(kName) + 1
End If

Me.SaveAs Filename:=kBaseName & "_ref_" &
Format(Evaluate(Me.Names(kName).RefersTo), "000") & ".xls"
Cancel = False

CleanUp:
Application.EnableEvents = True
End Sub


'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code