Auto Assign Numbers when Opening a Template

cdnqte

Board Regular
Joined
Jul 14, 2004
Messages
132
I am creating a work order template that I would like to automatically assign a work order number when a new one is opened. Is there a way to have this read another file to select consective numbers each time the template is used to open a new file?
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Oorang

Well-known Member
Joined
Mar 4, 2005
Messages
2,071
Yep
You can do it with a text stream or the Cheap way
Cheap way as is follows:
Read the last number... Make the next number the last number plus one.
 

cdnqte

Board Regular
Joined
Jul 14, 2004
Messages
132
But if it is a new form that is opened every time, how do I get it to read the last number?
 

Oorang

Well-known Member
Joined
Mar 4, 2005
Messages
2,071
Ahhhh...
Well actually I like your solution. Put a file on a shared drive, and just pull the information and then mark off the entry. But depending on how fast your network drive is tha may create some slowdown. But here is some code to play with. This assume your ordernumber sheet has order numbers in column A and column b will be marked with "Used" and eachnumber is used. Important:A1 and B1 should be column headers.


Code:
Sub GetOrdernumber()
Dim FN As String
Dim Ordernumber As String
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
Workbooks.Open Filename:="S:\My FOlder\Book1.xls"
FN = ActiveWorkbook.Name
Ordernumber = Range("B65536").End(xlUp).Offset(1, -1).Value
Range("B65536").End(xlUp).Offset(1, 0) = "Used"
ActiveWorkbook.Save
ActiveWindow.Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.EnableEvents = True
MsgBox Ordernumber
End Sub
 

cdnqte

Board Regular
Joined
Jul 14, 2004
Messages
132

ADVERTISEMENT

You have (almost) saved my sanity!! The code worked well and does almost what I need it to do! Instead of there being a message box, is it possible to have it drop the number into a specific cell? I am really new to VB so I have no idea how to do this!! I tried recording a macro and reading through the code but that didn't help me much. :eek:
 

Oorang

Well-known Member
Joined
Mar 4, 2005
Messages
2,071
To drop it into a specific cell you need only to specify the cell.
Range("a1") = Ordernumber

Some things to read through in the VBE helpfile would be offset, and end.

If I have everything in a flat file database (that is column headers are in row 1, every ro is a record, and the primary key is column A) I like to use this to add info to the bottom record.

Range("A65536").end(XLUP).offset(1,0) = OrderNumber

This says: from Cell A65536 do ctrl-up (which should take you to the bottommost record.) Then go down (offset) 1 row. Edn produc- you are on the bottom record.

There are several different ways to land on a range, this is just my personal favorite.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,069
Messages
5,570,014
Members
412,304
Latest member
citrus
Top