Purchase Order Generator

' Purchase Order Generator
' Suppliers sheet: SupplierID (A), Name (B), Address (C), Contact (D), Email (E), Terms (F)
' PO sheet: Purchase order form layout
' PO Log sheet: Historical record of all POs

Sub CreatePurchaseOrder()
    ' Generate a new purchase order
    On Error GoTo ErrorHandler

    Application.ScreenUpdating = False

    Dim wsPO As Worksheet
    Dim wsSuppliers As Worksheet
    Dim wsLog As Worksheet

    Set wsPO = ThisWorkbook.Sheets("Purchase Order")

    On Error Resume Next
    Set wsSuppliers = ThisWorkbook.Sheets("Suppliers")
    Set wsLog = ThisWorkbook.Sheets("PO Log")
    On Error GoTo ErrorHandler

    If wsSuppliers Is Nothing Then
        MsgBox "Please create a 'Suppliers' sheet with columns: SupplierID, Name, Address, Contact, Email, Terms", vbExclamation
        Exit Sub
    End If

    ' Create PO Log if it doesn't exist
    If wsLog Is Nothing Then
        Set wsLog = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
        wsLog.Name = "PO Log"
        wsLog.Cells(1, 1).Value = "PO Number"
        wsLog.Cells(1, 2).Value = "Date"
        wsLog.Cells(1, 3).Value = "Supplier"
        wsLog.Cells(1, 4).Value = "Total"
        wsLog.Cells(1, 5).Value = "Status"
        wsLog.Cells(1, 6).Value = "Approved By"

        With wsLog.Range("A1:F1")
            .Font.Bold = True
            .Interior.Color = RGB(0, 102, 204)
            .Font.Color = RGB(255, 255, 255)
        End With
    End If

    ' Clear previous PO
    wsPO.Range("A8:F50").ClearContents
    wsPO.Range("A8:F50").ClearFormats

    ' Generate PO number
    Dim poNumber As String
    Dim lastPONum As Long
    Dim lastLogRow As Long
    lastLogRow = wsLog.Cells(wsLog.Rows.Count, "A").End(xlUp).Row

    If lastLogRow > 1 Then
        ' Extract number from last PO
        Dim lastPO As String
        lastPO = wsLog.Cells(lastLogRow, 1).Value
        lastPONum = Val(Replace(lastPO, "PO-", ""))
    End If

    lastPONum = lastPONum + 1
    poNumber = "PO-" & Format(lastPONum, "0000")

    ' PO Header
    wsPO.Range("A1").Value = "PURCHASE ORDER"
    wsPO.Range("A1").Font.Size = 20
    wsPO.Range("A1").Font.Bold = True
    wsPO.Range("A1").Font.Color = RGB(0, 102, 204)

' ... 166 more lines hidden ...
'
' Sign up for free to view the complete code
' Visit: vbacode.io