AutoSequencing in a New Worksheet
 

Rahul:
With a slight mod, it will save the new workbook to the same folder as the
previous workbook.
Also, the procedure is now called by Workbook Open and some other code
cleanup.

Private Sub Workbook_Open()
NewPurchaseOrder
End Sub

Sub NewPurchaseOrder()
Dim vNum As Variant, vNewName As Variant
Dim y As Boolean, sPath As String
Dim fso As New FileSystemObject

Set fso = CreateObject("Scripting.FileSystemObject")

sPath = ThisWorkbook.Path

With ThisWorkbook.Worksheets("sheet1")
If .Range("B4").Value = "" Or _
Not IsNumeric(.Range("B4").Value) Then
'provide a default purchase order starting number
.Range("B4").Value = "10000"
Else
.Range("B4").Value = .Range("B4").Value + 1
vNum = .Range("B4").Value
End If
End With

Do
vNewName = sPath & "\" & vNum & ".xls"
y = fso.fileexists(vNewName)
If y = True Then
vNum = vNum + 1
End If
Loop Until y = False

ThisWorkbook.Worksheets("Sheet1").Range("B4").Valu e = vNum
ThisWorkbook.SaveAs Filename:=vNewName
End Sub

Regards
Jim Feaver

"Jim Feaver" wrote in message
news:sXcUb.390873$JQ1.275776@pd7tw1no...
Rahul:
One more thing: Use of FileSystem Object requires that the workbook has a
reference to Microsoft Scription Runtime. (Tools, References....)
Jim

"Jim Feaver" wrote in message
news:S27Ub.390074$JQ1.358834@pd7tw1no...
Rahul:
It appears that you are using one purchase order per workbook.

This works if the purchase order number is located in range B4 of
Sheet1.
As it stands, the "#" would need to be in B3, unless you add parsing
code).

Here is some code that can be in Workbook_Open event procedure
or else in a sub procedure called by it.


Private Sub Workbook_Open()
Dim vNum As Variant, vNewName As Variant
Dim y As Boolean
Dim fso As fileSystemObject

Set fso = CreateObject("Scripting.FileSystemObject")

With ThisWorkbook.Worksheets("sheet1")
MsgBox .Range("B4").Value
If .Range("B4").Value = "" Or _
Not IsNumeric(.Range("B4").Value) Then
.Range("B4").Value = "10000"
Else
.Range("B4").Value = .Range("B4").Value + 1
vNum = .Range("B4").Value
End If
End With
vNewName = vNum & ".xls"

Do
y = fso.fileexists(vNewName)
If y = True Then
vNum = vNum + 1
vNewName = vNum & ".xls"
End If
MsgBox vNewName, , "vNewName"

Loop Until y = False

ThisWorkbook.Worksheets("Sheet1").Range("B4").Valu e = vNum
ThisWorkbook.SaveAs Filename:=vNewName

End Sub

Regards,
Jim Feaver

"rahul3400 " wrote in message
...
Hello Guys,
I hope I could get some help!

I have an excel work-sheet, on the top-right corner of which there is
a
field called "PURCHASE ORDER #" with puchase order # 10001

The file is saved with the same name as its purchase order #
(10001.xls).

IS THERE A WAY TO AUTOMATICALLY PUT IN NEXT-IN-ORDER PURCHASE ORDER #,
ON THE NEW .XLS SHEET AS SOON AS YOU OPEN IT i.e. if the old sheet you
saved had number 10001, could we make new .xls sheet that we open,
have
10002 as default in the Purchase order field and so on.

Also, could we save the newly opened file at a location without
hitting
"Save" or "Save-As", with that new number (10002.xls) ?

Thanks,
Your help and advise will be sincerely appreciated!


---
Message posted from http://www.ExcelForum.com/