Thank you Dave,
I am afraid to say that I did not have the WORKS SHEET named WorkOrder, it
was the default Sheet1.
I since changed it, but the problem still occurs.

"Dave Peterson" wrote:

I'm not Jerry...

Do you have a worksheet in the activeworkbook named WorkOrder?

I bet not (check for leading/trailing spaces if you think you do).

Dane wrote:

I too would like to accomplish the same task as Steven Leuck, however I am
having trouble. I believe I followed exactly what you indicated but I keep
getting a Runtime Error 9. The line listed here is highlighted in yellow in
the error message.

Worksheets("WorkOrder").Range("WorkOrderNum") = WONum

Not sure what I am doing wrong, I would appreciate some help.
Could I send you my file?

Thank you,
Dane Watson

"JLatham" wrote:

Lets see if this variation of a theme from J.E. McGimpsey won't meet your
needs.

Use Notepad to create a file to contain your work order number. "Seed" it
with the value of the last used work order number. Save the file in the same
folder with your work order Excel file. For my example I called the file
WONums.txt

This code must go into the Workbook's _Open() event handler. To put it
there, open your work order workbook and right-click the Excel icon that is
immediately to the left of the word [File] in the menu toolbar and choose
[View Code] from the pop up list. Copy the code below and paste it into the
code window in the VB Editor that shows up. Close the VB Editor, save your
file and close it.

Next time you open it, the number in WONums.txt will be obtained,
incremented and placed into WorkOrderNum. In the code you'll see the line
that does that and I've assumed that the worksheet with that named range is
named WorkOrder. Change that sheet name as required for your workbook.

The last thing the routine does is save the workbook out into the same
folder using the work order # created as the filename.

Private Sub Workbook_Open()
Dim WONumberFile As String
Dim WONumbuffer As Integer
Dim WONum As Long

'file WONums.txt must be in same folder
'with this Excel file
'find where this file is at and use
'that path to find the WONums.txt file
WONumberFile = Left(Me.FullName, _
InStrRev(Me.FullName, Application.PathSeparator)) _
& "WONums.txt"
WONumbuffer = FreeFile()
'get the last Work Order Number used
Open WONumberFile For Input As #WONumbuffer
Input #WONumbuffer, WONum
Close #WONumbuffer
'increment last W.O.#
WONum = WONum + 1
'place new W.O.# on worksheet
'***** change worksheet name as required ****
Worksheets("WorkOrder").Range("WorkOrderNum") = WONum
'write the new number back to the WONums.txt file
WONumbuffer = FreeFile()
Open WONumberFile For Output As #WONumbuffer
Print #WONumbuffer, WONum
Close #WONumbuffer
'build new name for this file based on
'the assigned W.O.#
WONumberFile = Left(WONumberFile, _
InStrRev(WONumberFile, Application.PathSeparator)) _
& Trim(Str(WONum)) & ".xls"
'save the workbook under the new name
ThisWorkbook.SaveAs WONumberFile

End Sub


"Steven Leuck" wrote:

I am trying to write a Work Order entry form in Excel to simply a process and
automate what seems to be difficult for folks around here.

I have a Work Order form that has a number of data fill-in cells with other
cells protected. What I would like to do is two fold:
1) Have the spreadsheet automatically place a number in a cell called
"WorkOrderNum" that would be one more incrementally than the last file saved.
2) Have the Work Order worksheet automatically use that WorkOrderNum number
as the file name when it is saved.

The functionality is such that i'm hoping to have the non-computer users
here be able to open a single file which will automatically number itself as
outlined above and then save with the number for later reference if
necessary. Personally, I'd prefer using a single database with all these
numbers and worksheet data saved in each row/record. But, knowing how others
around here work, I don't think that will be possible.

I've poked through the Help system and read several entries here in the
forums but don't see exactly what I'm looking for. Any help or suggestions
are appreciated.
--
Steven Leuck
Builders Electric, Inc.

--

Dave Peterson