unique number

[Chaindriv]

I am trying to make a template that can give each new spreadsheet a unique number ie. Work Order 1, Work Order 2, etc. How can I make this either on open or with an assigned macro button?

[Tom Urtis]

Sometimes, a simple approach is best. This question can be answered two ways in my opinion.

The first way (simple) is to have you enter the text "Work Order #" (without the quotes) in cell A1 of sheet1.

Then, paste this macro into a standard module, or copy & paste only the one line
Sheet1.[B1] = Sheet1.[B1] + 1
into a Workbook_Open event module:

Sub Auto_Open()
Sheet1.[B1] = Sheet1.[B1] + 1
End Sub

Every time you open your workbook, A1 and B1 will look like
Work Order # 1
and then
Work order # 2, etc, incrementing upward by one.

The more complicated solution would be to write code that takes into account the text "Work Order #" if you want that with the actual number in one cell. The code is not difficult, just more complicated-looking for your co-workers who may have to modify the file in your absence.

This first simple option is more intuitive for people to understand, and needs no adjustment, if for example your text changes from "Work Order #" to "Invoice Number".

If you need a different solution that what I gave here, please repost.

Tom Urtis

[Paul B]

I read the post wrong, I thought you wanted to save the file with a new number. Anyway..

It want work as a template, maybe someone can fix it, but maybe it will help, put your first work order number in AA1, put Work Orders in AC1 the Marco will save the workbook by the name in AC1 and the number in AB1, AB1 will change by one number every time the Marco is run. Assign the Marco to a button if you want, also you can change the cell reference if you need to, if you want it to run when the workbook is opened just put Application.Run " Save_By_Number " in this workbook code. Hope this helps Paul B

Sub Save_By_Number()
Application.ScreenUpdating = False
lastnumber = Worksheets("sheet1").Range("AA1").Value
nextnumber = lastnumber + 1
Worksheets("sheet1").Range("AB1").Value = nextnumber
Worksheets("sheet1").Range("AA1").Value = nextnumber
FileName = Worksheets("sheet1").Range("AC1").Value & Worksheets("sheet1").Range("AB1").Value
ActiveWorkbook.SaveAs (FileName)
Application.ScreenUpdating = False
End Sub


[ This Message was edited by: Paul B on 2002-02-18 20:27 ]

[Chaindriv]

Thanks guys, between the two posts, I have corrected the problem