This follow-up video to episode 1505 shows how to automatically create an invoice register as your are using one of the Invoice Templates from Excel.
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1808 – Create an Invoice Register.
Hey. Welcome back to MrExcel netcast. I’m Bill Jelen. Well, one of my most popular videos out of YouTube is episode 1505: “Next Invoice Number”.
This shows how to take a template from Office Online and add a little bit of Macro language to increment the invoice number, save the invoice out to its own file.
We got a question a few days ago: “Thanks so much for this video. My boss loves it.
He loves it so much that he’s challenged me to figure out a way to populate a master spreadsheet with each invoice’s information automatically after it’s saved” – we’re actually going to do it before it’s saved, or in the process of saving it – “I thought it would be easy – just link the cell to another cell in another spreadsheet, but then it occurred to me that each invoice is a separate sheet, so this information would just continue to update over the old information.
Any awesome way to do this?” Yes!
Absolutely, there’s an awesome way to do this. So, here we go.
Microsoft Excel, “File”, “New”. We’re going to search for “Invoices”.
“Sales Invoices”.
Alright, so, here’s the invoice.
We’re going to go back and add the same code that we added to the Episode 1505, but let’s talk about how to do an Invoice Register.
First thing, “Simple Invoice” – I hate that – let’s just call this “Invoice”, or something like that.
Get rid of the space.
We’re going to add a new sheet called “Register”, as in “Invoice Register”.
So, this is going to be our Invoice Register, and we have to think about what fields our manager wants.
He probably wants “Date”, “Invoice Number”, “Customer”, and “Amount”. At least that much.
There might be more fields. So, if you have more fields, that’s okay, we’re going to have to just add the same logic that we’re going to do for these fields.
Alright, so, we have our new sheet called “Register”. We have our new renamed sheet called “Invoice”. This is out there in Compatibility Mode, so we have to do “File”, “Save As”.
And it has to be a Macro-Enabled Workbook, so make sure to change that.
And we’re just going to call it “InvoiceProgram”.
Alright, good. Now, we’re going to make a few notes here. I have a notepad – you can’t see the notepad, it’s an actual, physical post-it note – in my hand.
I’m going to know that Invoice Number is in cell C5. C5 is Invoice Number.
The Invoice Date is in C4. The Customer, I’m going to use cell A10, and, let’s see… the Total.
Alright, now, maybe they’re going to insert new lines, so I’m going to come here to this “Total” cell, and I’m going to just call it “Invoice Total”… “InvTot”, it has to be one word, no space.
So the range name “InvTot” is our Total Sales.
So those are the 4 fields that we want to capture in the Invoice Register.
Alright. We’re going to press Alt+F11 to switch over to VBA.
We’ll do “Insert”, “Module”.
Good. And I’m going to add in the code from episode 1505, hang on.
Okay, so, there you go, I actually just typed that code, being careful that in the other video (which you have to watch to get the explanation of this), it said to make sure to customize the cell that has the Invoice Number.
In the old video that was cell E5.
In this particular template that I downloaded today, it’s C5. Alright, so, now, we’re going to add some new code up here.
I’m going to call this “Sub PostToRegister”.
And we’re going to “Dim WS1 As Worksheet”, “Dim WS2 As Worksheet”, “Set WS1 = Worksheets”.
I call it “Invoice” (if you can’t remember that, it’s right over here in the Project Explorer). And “Set WS2 = Worksheets (“Register”)”.
Alright, those have to match. Now, first thing we’re going to do: I’m going to add some comments here: “ ‘ Figure out which row is the next row”, and then “ ‘ Write the important values to Register”.
Alright: “Figure out which row is the next row”. First off, we have to make sure that we are using a field that will always be filled in.
No blanks, so, probably “Date” or “Inv #” are really a safe one to go.
So “Date” is in Column A, that’s Column 1. Alright, so, “NextRow = WS2.Cells (Rows.Count, 1)”.
Right there, that “1” – if your Column A has a field that is not always filled in, like “Salutation”, or “Apartment Number”, don’t use Column 1, use some other column that’s always going to be filled in.
“.End(xlUp).Row + 1”.
Alright, so, this will tell us where we should write this Invoice to.
And I’m just going to test it, so let’s do a little message box here, “NextRow”.
Just to make sure.
So, we have Title in Row 1, blank in Row 2, Headings in Row 3.
The answer here better be a “4”.
Alright, so we’ll just run this, click the “Run” button, and, sure enough, we get a 4, alright?
So, that’s good, that means that that code is working.
I can get rid of the message box.
And then what we’re going to do is we’re going to say: “WS2” – that’s our Register worksheet – “.Cells(NextRow, 1).Resize” 1 Row, 4 Columns. Now, if you’re collecting more information than the 4 columns than I’m collecting – which I’m sure you are – that number is going to be bigger than 4, it’s going to be 5, 6, 7, 8, 9, whatever it is, “.Value = Array”, and then, we’re going to put the 4 values that we want.
The 4 values, so in my case it’s Date first, so that’s “(WS1.Range(“C4”)”.
I’m getting that from the notepad that I wrote that down on.
And then “WS1.Range(“C5”)” is the Invoice Number.
And then we need the Customer.
The Customer is coming from A10.
So, we’re going to go off the screen here, I’m going to put the continuation character – that’s a space and an underscore – and go down to the next line.
“WS1.Range(“A10”)”, and then, finally, the Range called “Invoice Total”.
And we will close the Array.
Alright, so, what this tiny little Macro does is, it figures out where the next row is, and then it writes all the information from this invoice onto that row.
Alright, so, here is a test. Let’s come back and we will have our Invoice 12345, today’s date, and the customer is going to be “Joe’s Computer Books”.
And we’re going to sell them 10 copies of “Slaying Excel Dragons” for $299.95, and 15 copies of “Don’t Fear the Spreadsheet”.
Let’s see, that’s equal 15 times $14.95.
And they should buy one copy of “VBA and Macros for Excel 2013”.
If you’re watching this and you want to learn Macros, that’s a great book, $39.95.
Alright, we’ll just scroll down here and see what our total is, make sure that we’ve got it right, $564.15.
Alright, let’s run our code, I’ll come back here, click inside the Macro, click “Run”, and when I go look at the Register, we should see that we have the date, the invoice, the customer, and the amount. Alright, the date needs to be formatted as a date.
Alright, but it looks good. And if we would happen to run that Macro again, (which, in real life, we would never run it twice), “PostToRegister”, see it’s going to the next row. That’s awesome!
Okay, so, we take this code, “PostToRegister”, and when do we want to run it?
We have 2 Macros already, “SaveWithNewName”, that makes a copy of the workbook to a new place, and then it calls “NextInvoice”. Well, right here, before we make that copy, that’s when we want to “PostToRegister”.
PostToRegister… There we go.
Alright, so, here’s our old Macro that was calling “NextInvoice”, now it’s going to call “PostToRegister”, so we’re going to write the things to the Register, we’re going to make a copy and save it, and then increment the Invoice by 1, and we will be all set to go.
So, if you’re watching this video, you want to go back and watch, first, this video from a while ago: Episode 1505, that will show you how to set up this code and explain this code. And then to add the Invoice Register, just add in this new, tiny, little Macro here, because I’m only collecting 4 cells, in real life you certainly would be collecting more cells.
Now, hey, I have to caution you.
When I started to think about this, if you need to gather all of the items here, that really needs to be saved to a new sheet, like a “Line Item Detail Register Sheet” or something like that.
Don’t try and put this on the Register Sheet. The Register Sheet just wants to have one line per invoice with your totals.
If you need the line item details, then put that on a second sheet.
You can join those back together using PowerPivot or a lot of VLookups.
Your call.
[ music ] Hey. I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast, episode 1808 – Create an Invoice Register.
Hey. Welcome back to MrExcel netcast. I’m Bill Jelen. Well, one of my most popular videos out of YouTube is episode 1505: “Next Invoice Number”.
This shows how to take a template from Office Online and add a little bit of Macro language to increment the invoice number, save the invoice out to its own file.
We got a question a few days ago: “Thanks so much for this video. My boss loves it.
He loves it so much that he’s challenged me to figure out a way to populate a master spreadsheet with each invoice’s information automatically after it’s saved” – we’re actually going to do it before it’s saved, or in the process of saving it – “I thought it would be easy – just link the cell to another cell in another spreadsheet, but then it occurred to me that each invoice is a separate sheet, so this information would just continue to update over the old information.
Any awesome way to do this?” Yes!
Absolutely, there’s an awesome way to do this. So, here we go.
Microsoft Excel, “File”, “New”. We’re going to search for “Invoices”.
“Sales Invoices”.
Alright, so, here’s the invoice.
We’re going to go back and add the same code that we added to the Episode 1505, but let’s talk about how to do an Invoice Register.
First thing, “Simple Invoice” – I hate that – let’s just call this “Invoice”, or something like that.
Get rid of the space.
We’re going to add a new sheet called “Register”, as in “Invoice Register”.
So, this is going to be our Invoice Register, and we have to think about what fields our manager wants.
He probably wants “Date”, “Invoice Number”, “Customer”, and “Amount”. At least that much.
There might be more fields. So, if you have more fields, that’s okay, we’re going to have to just add the same logic that we’re going to do for these fields.
Alright, so, we have our new sheet called “Register”. We have our new renamed sheet called “Invoice”. This is out there in Compatibility Mode, so we have to do “File”, “Save As”.
And it has to be a Macro-Enabled Workbook, so make sure to change that.
And we’re just going to call it “InvoiceProgram”.
Alright, good. Now, we’re going to make a few notes here. I have a notepad – you can’t see the notepad, it’s an actual, physical post-it note – in my hand.
I’m going to know that Invoice Number is in cell C5. C5 is Invoice Number.
The Invoice Date is in C4. The Customer, I’m going to use cell A10, and, let’s see… the Total.
Alright, now, maybe they’re going to insert new lines, so I’m going to come here to this “Total” cell, and I’m going to just call it “Invoice Total”… “InvTot”, it has to be one word, no space.
So the range name “InvTot” is our Total Sales.
So those are the 4 fields that we want to capture in the Invoice Register.
Alright. We’re going to press Alt+F11 to switch over to VBA.
We’ll do “Insert”, “Module”.
Good. And I’m going to add in the code from episode 1505, hang on.
Okay, so, there you go, I actually just typed that code, being careful that in the other video (which you have to watch to get the explanation of this), it said to make sure to customize the cell that has the Invoice Number.
In the old video that was cell E5.
In this particular template that I downloaded today, it’s C5. Alright, so, now, we’re going to add some new code up here.
I’m going to call this “Sub PostToRegister”.
And we’re going to “Dim WS1 As Worksheet”, “Dim WS2 As Worksheet”, “Set WS1 = Worksheets”.
I call it “Invoice” (if you can’t remember that, it’s right over here in the Project Explorer). And “Set WS2 = Worksheets (“Register”)”.
Alright, those have to match. Now, first thing we’re going to do: I’m going to add some comments here: “ ‘ Figure out which row is the next row”, and then “ ‘ Write the important values to Register”.
Alright: “Figure out which row is the next row”. First off, we have to make sure that we are using a field that will always be filled in.
No blanks, so, probably “Date” or “Inv #” are really a safe one to go.
So “Date” is in Column A, that’s Column 1. Alright, so, “NextRow = WS2.Cells (Rows.Count, 1)”.
Right there, that “1” – if your Column A has a field that is not always filled in, like “Salutation”, or “Apartment Number”, don’t use Column 1, use some other column that’s always going to be filled in.
“.End(xlUp).Row + 1”.
Alright, so, this will tell us where we should write this Invoice to.
And I’m just going to test it, so let’s do a little message box here, “NextRow”.
Just to make sure.
So, we have Title in Row 1, blank in Row 2, Headings in Row 3.
The answer here better be a “4”.
Alright, so we’ll just run this, click the “Run” button, and, sure enough, we get a 4, alright?
So, that’s good, that means that that code is working.
I can get rid of the message box.
And then what we’re going to do is we’re going to say: “WS2” – that’s our Register worksheet – “.Cells(NextRow, 1).Resize” 1 Row, 4 Columns. Now, if you’re collecting more information than the 4 columns than I’m collecting – which I’m sure you are – that number is going to be bigger than 4, it’s going to be 5, 6, 7, 8, 9, whatever it is, “.Value = Array”, and then, we’re going to put the 4 values that we want.
The 4 values, so in my case it’s Date first, so that’s “(WS1.Range(“C4”)”.
I’m getting that from the notepad that I wrote that down on.
And then “WS1.Range(“C5”)” is the Invoice Number.
And then we need the Customer.
The Customer is coming from A10.
So, we’re going to go off the screen here, I’m going to put the continuation character – that’s a space and an underscore – and go down to the next line.
“WS1.Range(“A10”)”, and then, finally, the Range called “Invoice Total”.
And we will close the Array.
Alright, so, what this tiny little Macro does is, it figures out where the next row is, and then it writes all the information from this invoice onto that row.
Alright, so, here is a test. Let’s come back and we will have our Invoice 12345, today’s date, and the customer is going to be “Joe’s Computer Books”.
And we’re going to sell them 10 copies of “Slaying Excel Dragons” for $299.95, and 15 copies of “Don’t Fear the Spreadsheet”.
Let’s see, that’s equal 15 times $14.95.
And they should buy one copy of “VBA and Macros for Excel 2013”.
If you’re watching this and you want to learn Macros, that’s a great book, $39.95.
Alright, we’ll just scroll down here and see what our total is, make sure that we’ve got it right, $564.15.
Alright, let’s run our code, I’ll come back here, click inside the Macro, click “Run”, and when I go look at the Register, we should see that we have the date, the invoice, the customer, and the amount. Alright, the date needs to be formatted as a date.
Alright, but it looks good. And if we would happen to run that Macro again, (which, in real life, we would never run it twice), “PostToRegister”, see it’s going to the next row. That’s awesome!
Okay, so, we take this code, “PostToRegister”, and when do we want to run it?
We have 2 Macros already, “SaveWithNewName”, that makes a copy of the workbook to a new place, and then it calls “NextInvoice”. Well, right here, before we make that copy, that’s when we want to “PostToRegister”.
PostToRegister… There we go.
Alright, so, here’s our old Macro that was calling “NextInvoice”, now it’s going to call “PostToRegister”, so we’re going to write the things to the Register, we’re going to make a copy and save it, and then increment the Invoice by 1, and we will be all set to go.
So, if you’re watching this video, you want to go back and watch, first, this video from a while ago: Episode 1505, that will show you how to set up this code and explain this code. And then to add the Invoice Register, just add in this new, tiny, little Macro here, because I’m only collecting 4 cells, in real life you certainly would be collecting more cells.
Now, hey, I have to caution you.
When I started to think about this, if you need to gather all of the items here, that really needs to be saved to a new sheet, like a “Line Item Detail Register Sheet” or something like that.
Don’t try and put this on the Register Sheet. The Register Sheet just wants to have one line per invoice with your totals.
If you need the line item details, then put that on a second sheet.
You can join those back together using PowerPivot or a lot of VLookups.
Your call.
[ music ] Hey. I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
