How to Make Invoices In Excel

Hey there!

Sign up below and I will email you the Excel workbook with VBA code shortly.

You’ll also get several more free resources over the coming days that I hope you find helpful!

If you don’t like free stuff and just want the VBA code, here it is 👇

Sub PrintAreaToPDF()
    Dim ws As Worksheet
    Dim ws2 As Worksheet
    Dim filePath As String
    Dim fileName As String
    Dim invoiceNum As String
    Dim clientRef As String
    Dim cell As Range
    
    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("invoice")
    Set ws2 = ThisWorkbook.Sheets("data")
    
    ' Get values from cells
    invoiceNum = ws.Range("F7").Text  ' Using .Text instead of .Value to get formatted value
    clientRef = ws.Range("C5").Text
    
    ' Set the file path - modify this to your desired location
    filePath = "C:\Users\Eamonn\Desktop\"  ' Make sure this folder exists
    
    ' Create filename with cell values
    fileName = "Invoice_" & invoiceNum & "_" & clientRef & ".pdf"
    
    ' Remove any invalid characters from filename
    fileName = Replace(fileName, "\", "")
    fileName = Replace(fileName, "/", "")
    fileName = Replace(fileName, ":", "")
    fileName = Replace(fileName, "*", "")
    fileName = Replace(fileName, "?", "")
    fileName = Replace(fileName, """", "")
    fileName = Replace(fileName, "<", "")
    fileName = Replace(fileName, ">", "")
    fileName = Replace(fileName, "|", "")
    
    ' Make sure the directory exists
    If Dir(filePath, vbDirectory) = "" Then
        MkDir filePath
    End If
    
    ' Export to PDF using the predefined print area
    With ws.PageSetup
        ' Optional: Set PDF properties if needed
        .Zoom = False            ' Use default zoom
        .FitToPagesTall = 1      ' Fit to 1 page tall
        .FitToPagesWide = 1      ' Fit to 1 page wide
        .CenterHorizontally = True
        .CenterVertically = True
    End With
    
    ' Export the print area to PDF
    ws.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        fileName:=filePath & fileName, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True

    ws.Range("C5").ClearContents
    ws.Range("C13:D27").ClearContents
    
        ' Loop through the range A26:A125
    For Each cell In ws2.Range("A26:A125")
        ' If we find a TRUE value, change it to FALSE and exit
        If cell.Value = False Then
            cell.Value = True
            Exit For
        End If
    Next cell
    
    
    ' Optional: Show confirmation message
    MsgBox "PDF has been created: " & vbNewLine & filePath & fileName, vbInformation
End Sub

Happy Spreadsheeting!

Cheers, Eamonn
Connect on LinkedIn, YouTube & Twitter.