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.
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.
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)
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”.
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.
___________________
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!
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
where’s the file? :3