Auto numbering

[Adam]

I have created a template for a sales information sheet. I would like it to
generate a new unique sequential number starting at 100001 every time that
the template is opened. when the file is closed I wold like it to be saved
as a .xls using the unique number plus customer name as the file name.

Can this be done?

 

[Nigel]

You will need to track the current used value somewhere in the system. One
way would be to store the value in the template, so that when
it is opened the number is incremented and the template is re-saved back.
After that the close event can be used to save the workbook by building the
filename string comprising the unique key (in A1 in example below) and
customer name (in A2 in example below). Excel 2007 code change the SaveAs
code in open event to suit xl2003 if needed.

Private Sub Workbook_Open()
With Sheets("Sheet1").Range("A1")
.Value = .Value + 1
End With
Application.DisplayAlerts = False
ThisWorkbook.SaveAs _
Filename:="D:\myTemplate.xltm", _
FileFormat:=xlOpenXMLTemplateMacroEnabled
Application.DisplayAlerts = False
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sFilename As String
With Sheets("Sheet1")
sFilename = .Range("A1") & Trim(.Range("A2"))
End With
ThisWorkbook.SaveAs "D:\" & sFilename & ".xls"
End Sub