Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating Sequential Numbering Macro for an Quotation template
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating Sequential Numbering Macro for an Quotation template
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 "steewwy" wrote in message ups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating Sequential Numbering Macro for an Quotation template
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 -- HTH Bob Phillips "steewwy" wrote in message ups.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro for sequential numbering. | Excel Discussion (Misc queries) | |||
Sequential Numbering in a Template | Excel Discussion (Misc queries) | |||
PO with sequential numbering with start / end numbering | Excel Discussion (Misc queries) | |||
Creating part # tags using sequential numbering. | Excel Discussion (Misc queries) | |||
Work Order Template Numbering Macro | Excel Programming |