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.

___________________

You may also like...

3 Responses

  1. Janice Liberty says:

    Hi, I would like to create a macro to update the year, start date, and end date on a datasheet and possible have the update point to another sheet in the same workbook.
    Year = 2008 and 2009
    Date Ranges:
    9/1/2008 – 12/15/2008
    91/7/2009 – 5/1/2009
    5/15/2009 – 8/15/2009
    Thanks!

  2. maarouf says:

    Hi, I am desperately looking for a macro that will run an automated invoice number with the year and month followed by the number. eg. 2013/03/001 automated it should read he next invoice number “2013/03/002”

    Secondly I would like the month and invoice number to be reset at the begining of each month
    eg. assuming I am in the month of “march” the last invoice number “2013/03/299 come “april” i would like it to be reset to “2013/04/001

    Appreciate your help in advance

  3. mark says:

    where’s the file? :3

Leave a Reply to Janice Liberty Cancel reply

Your email address will not be published. Required fields are marked *