Purchase Order Increment

**knightrider99** · 11-14-2012, 05:11 AM

Hi guys,

Attached is a purchase order excel file. What I want to do is get the purchase order field to increment by one every time someone opens the master template. They will then save the file as a normal excel worksheet.
However I can’t get it to increment each time it opens. I am starting to tear my hair out.

Any help much appreciated!!

Knightrider99

**Marcol** · 11-14-2012, 06:22 AM

Try this in the Workbook Module

Private Sub Workbook_Open()
    Dim OrderNo As String, Prefix As String
    Dim strNumber As String
    
    OrderNo = Range("F3")
    Prefix = Left(OrderNo, 2)
    strNumber = Right(OrderNo, 6)
    strNumber = Format(CLng(strNumber) + 1, "00000#")
    
    Range("F3") = Prefix & strNumber
    
    ActiveWorkbook.Save
End Sub

If you prefer to you can use this code, the above is just a long version to make it easier to understand

Private Sub Workbook_Open()
        
    Range("F3") = Left(Range("F3"), 2) & Format(CLng(Right(Range("F3"), 6)) + 1, "00000#")
    
    ActiveWorkbook.Save
    
End Sub

First save this file to your desired location.

When the file is opened, Macros must be enabled for this to work.