same template on each new sheet in a workbook?

**MrinjuredBen** · 02-01-2022, 01:33 AM

G'day all,

My first post here so hopefully someone can steer me in the right direction. While I am laying in bed recovering from a fight with an angle grinder, I decided to sort out my excel based invoicing for 2022.

Long story short,

I want a workbook where each new sheet loads as my excel invoice template. Second to that, I'd like the invoice number to advance by one on each new sheet.

Is this within the realms of possibility? If so, how would I go about adding this functionality to a workbook?

Sincerely,

Injured Ben

**dflak** · 02-04-2022, 01:53 PM

This is actually quite simple to do, but if you can provide us with a sample workbook with a copy of your invoice template it would help a lot.

**MrinjuredBen** · 02-04-2022, 05:30 PM

That sounds fair enough. Here is a redacted version of my invoice template.

**torachan** · 02-04-2022, 05:41 PM

Surely you would be better to use a single template with the contents data saved in a separate audit trail sheet, you can then save and print to a pdf.

**MrinjuredBen** · 02-04-2022, 05:46 PM

haha probably. It's only a very small handyman business, so the grand plan is for an entire financial years worth of invoices to sit as a collection of sheets, with a summary sheet at the end of the year that pulls data from each of the invoices in previous sheets. For now, how do I get the invoice number to advance for each new sheet, and for each new sheet to automatically display the invoice template? I'm really only a very novice Excel user.

**dflak** · 02-04-2022, 10:34 PM

I've done a similar kind of application before.

It starts with the control panel. I use a sheet called Control Panel to contain controls to download data, run macros and do other scratch calculations.

Don't mess with any of the calculations in Column B. Next Invoice keeps the running total for the invoice and the next line in the database to accept data.

All you have to do is click on the Create Invoice Button. This copies the Invoice Template sheet to a new sheet with the name of the invoice.

I modified the Invoice Template with some data validation. For example, you can only enter a date if it is today or earlier. I applied some cell formatting (like currency) and I protected the sheet. You can only enter data into cells that you should be entering data into. Things like the date and invoice number are in locked cells. These are filled in by the program.

Fill out the invoice sheet and then click on the Post Invoice Button. This creates a PDF file in the same directory with wherever you save this spreadsheet. The Button does not print in this copy.

When it is done, the invoice sheet you just filled out is deleted from the workbook.

Although you did not ask for it, I threw in a database function. It "scrapes" the invoice before downloading it. It records the date, invoice number, Bill to information, Amount and due date. It also has a flag to indicate if the invoice has been paid. It comes in as FALSE and you can update this manually as invoices are paid. The table is set up so you can use pivot tables to analyze it for open / closed invoices, jobs per month, dollar amount per month, etc.

The last thing the publish subroutine does is increment the invoice number counter.

Eventually the directory will fill up with PDF files for invoices. You may want to archive them (move them to another directory). You might want to move paid invoices to a paid directory, When you get to that point, post again. The database will help with that