One Click Invoices in Excel (VBA)

In today’s email:

1. Formatting an invoice

2. Dynamically filling it

3. Adding VBA to automate it

4. Adding buttons to enable one-click save to PDF

Got Sheet is brought to you by HubSpot.
HubSpot’s Starter Customer Platform includes the Starter edition of HubSpot’s six core products for marketing, sales, and customer service — all powered by HubSpot’s Smart CRM.
Get started free.

MAIN ARTICLE

Video tutorial:

Invoicing in Excel

Today’s video tutorial is especially useful for business owners, operators and entrepreneurs.

We’ll build a workbook that pulls in and formats customer data, product information, pricing, discounts and taxes.

With one click, the whole thing is saved to our desktop as a PDF.

Then, the template resets, increments the invoice number and is ready for our next invoice.

We use XLOOKUP extensively in building the invoice, and we dive into VBA to create the automations.

Then we create a button inside our workbook that executes the VBA code when clicked.

There are two ways to create the button - one using the Developer tab and one using a simple shape. I’ll walk through them both.

You can click the video above to watch the whole thing, or skip to a particular section by selecting a timestamp below:

• Demo

• Setup

• Invoice Template

• Customer List

• Two space thing

• Auto-increment invoice numbers

• Item dropdown list

• Totals and discounts

• VBA walkthrough

• Button #1

• Button #2

Learn without limits on Coursera.
Google’s latest course is Prompting Essentials. Learn to give clear and specific instructions to generative AI in 5 easy steps.
Enroll here.

THE CODE

Sample Workbook and VBA

Grab a copy of the Excel workbook I used here.

Here’s the link to download the Excel workbook.

Select File, Create a Copy, Download a Copy:

You’ll probably be greeted by a security warning when you open it:

To get around this, you can unblock the file by selecting properties and clicking Unblock:

You can also save it to your OneDrive before downloading as detailed here.

Here’s the whole VBA code pasted for you again in case you prefer to just copy/paste it into a new workbook.

Make sure you remember to save it as a macro-enabled workbook in order to use it.

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

NEXT STEPS

Whenever you’re ready, here’s how I can help:

1. Business tech stack (FREE)
   My recommendations for software and tools I use to create content and run my online business.

2. Sponsor Got Sheet
   Got Sheet teaches business operators, teachers and entrepreneurs how to get good at spreadsheets. Interested in sponsoring an issue or a series of Got Sheet newsletters? Reach out to get more information.

3. Personal budget tool
   As a Got Sheet subscriber, I want you to have a 75% discount on the personal budget I built 10+ years ago and am still using to this day.

HAPPY SPREADSHEETING!

Enjoyed this issue?
Subscribe below or Leave a testimonial

Cheers, Eamonn
Connect on LinkedIn, YouTube & Twitter.