Auto Sequecing in New Work Sheet

  • Thread starter Thread starter rahul3400
  • Start date Start date
R

rahul3400

Hello Guys,
I hope I could get some help!

I have an excel work-sheet, on the top-right corner of which there is
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 yo
saved had number 10001, could we make new .xls sheet that we open, hav
10002 as default in the Purchase order field and so on.

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

Thanks,
Your help and advise will be sincerely appreciated
 
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!
 
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").Value = vNum
ThisWorkbook.SaveAs Filename:=vNewName

End Sub

Regards,
Jim Feaver
 
Rahul:
One more thing: Use of FileSystem Object requires that the workbook has a
reference to Microsoft Scription Runtime. (Tools, References....)
Jim

Jim Feaver said:
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").Value = vNum
ThisWorkbook.SaveAs Filename:=vNewName

End Sub

Regards,
Jim Feaver
Click to expand...
 
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").Value = vNum
ThisWorkbook.SaveAs Filename:=vNewName
End Sub

Regards
Jim Feaver

Jim Feaver said:
Rahul:
One more thing: Use of FileSystem Object requires that the workbook has a
reference to Microsoft Scription Runtime. (Tools, References....)
Jim
Click to expand...
 
Back
Top