Print Invoices With Excel Macro

There is a sample file on my Contextures site, in which you can enter invoice details, then print all the new invoices by clicking a button.

invoiceprint01

I’ve updated the file, and you can now download the xlsm version, if you’re using Excel 2007 or a later version.

Store Invoice Details in Table

In the new version, the invoice data is stored in a named Excel table, and a named range – Database – is based on that table. The range is dynamic, because it is based on the named table, so it will include new rows as they are added.

Invoices that have been printed are marked with an X, and the new invoices do not have a mark in column A.

invoiceprint02

Count Unprinted Invoices

On the Invoice sheet, you can see how many invoices have not been printed. A formula calculates that number by counting invoice numbers and subtracting the count of X marks:

=COUNTA(Data!B2:B25)-COUNTA(Data!A2:A25)

invoiceprint03 

Print the Invoices

When you click the Print Invoices button, a macro filters the list, to show only the records with a blank cell in column A. This code is quite different from the previous version, because it uses List AutoFilter VBA, which is only available in newer versions of Excel.

Then, for each of those records, the invoice is printed, and then the record is marked with an “X”.

invoiceprint04

Download the Sample File

To see how the invoice printing macro works, and to view the code, you can download the sample file. On the Sample Spreadsheets page, go to the Functions section, and look for FN0009 – Print Unmarked Invoices

The zipped file is in Excel 2007/2010 format (xlsm), and contains macros.

___________________