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
Which lookup functions find a value equal or greater than the lookup value?
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.
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"
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.EnableEvents = True
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.