unique number

Chaindriv

New Member
Joined
Feb 17, 2002
Messages
2
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?
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top