The Question: "How do I generate the next Invoice Number?" Microsoft Office Online offers a great number of Invoice Templates. However, none of those templates has a Macro to pull the next Invoice Number. Today, in Episode #1505, Bill shows us how to 'Macro Enable' our Workbook and then create a short VBA Code routine to automatically generate Invoice Numbers with the press of a mouse button.
Note: A similar discussion happened in episodes 742-745. You might want to review those videos as well, as they go the extra step of creating an add-in.
PLEASE - if you need to save as PDF or if you alphanumeric invoice numbers, read through the FAQ at Next Invoice Number. Your question has already been answered many times in the comments below.
New on October 16 2013 - a follow-up video, episode 1808 shows how to add an automatic Invoice Register to this code. See
Table of Contents
(0:00) Introduction & Welcome to Excel Next Invoice Number
(0:15) Downloading an Invoice from Office Online
(0:40) Remember where the invoice number cell is
(1:00) Save as Macro-Enabled Workbook
(1:25) Switch to the VBA Editor with Alt+F11
(1:45) NextInvoice Macro
(2:20) Clearing out old data in invoice
(2:45) Using .ClearContents
(2:57) Running the macro from a Shape
(3:34) Running the code the first time
(3:42) Save each invoice before clearing
(5:05) Make sure to save before filling in invoice
(5:44) Wrap Up
This episode is the video podcast companion to the book, "Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Note: A similar discussion happened in episodes 742-745. You might want to review those videos as well, as they go the extra step of creating an add-in.
PLEASE - if you need to save as PDF or if you alphanumeric invoice numbers, read through the FAQ at Next Invoice Number. Your question has already been answered many times in the comments below.
New on October 16 2013 - a follow-up video, episode 1808 shows how to add an automatic Invoice Register to this code. See
Table of Contents
(0:00) Introduction & Welcome to Excel Next Invoice Number
(0:15) Downloading an Invoice from Office Online
(0:40) Remember where the invoice number cell is
(1:00) Save as Macro-Enabled Workbook
(1:25) Switch to the VBA Editor with Alt+F11
(1:45) NextInvoice Macro
(2:20) Clearing out old data in invoice
(2:45) Using .ClearContents
(2:57) Running the macro from a Shape
(3:34) Running the code the first time
(3:42) Save each invoice before clearing
(5:05) Make sure to save before filling in invoice
(5:44) Wrap Up
This episode is the video podcast companion to the book, "Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
Learn Excel from MrExcel podcast, episode 1505 – Next Invoice Number.
Hey.
Welcome back to the MrExcel netcast.
I’m Bill Jelen from MrExcel.com.
The question sent in today is how do I generate the next invoice number?
I’ve set up an invoice.
How do I make it generate the next invoice?
Well, first of all, we are going to start with an invoice, that’s easy.
File, New.
Out here on Office Online they have all kinds of invoices to choose from.
I mean there’s folders and folders of these.
We’ll just choose one at random.
The concepts here are going to be similar, no matter which one you choose.
Alright.
So we’ve opened that from Office Online.
Obviously, some customisations here.
You want to go through and put your company name, address, and all that stuff.
But figure out where the invoice number is.
And I don’t like this.
They put the invoice number in square brackets.
That’s kind of weird.
I’m going to put 23451 so it’s actually numeric.
And I’m going to remember that that is cell E5.
Okay, now.
I’ve pulled this down and I need to save it. Now right now it’s in compatibility mode so it’s actually… Will work for Excel 2003.
I’m going to do File, Save As, save this on my local machine and make sure that it’s a Macro-Enabled Workbook.
If you usually save things as Excel workbooks, you need to go Macro-Enabled.
Alright.
We’ll just call it ‘MrExcelInvoice’.
Cool.
Alright.
So now we have a local copy saved and macros are ‘legal’.
Now, to get over to the macro language, we’re going to do Alt F11.
Don’t be afraid of this.
It’s just a tiny bit of code.
If you’ve never been to the macro language before, it opens up to this big grey screen.
View, Project Explorer.
In the Project Explorer, find the project called whatever you’ve saved it as.
And then we’re going to insert a module.
Insert a Module.
We’re going to create two 2 macros.
‘Sub NextInvoice()’.
Alright.
And what we do is we say, ‘Range (“E5”).Value = Range(“E5”).Value + 1’.
Right, now, whichever invoice you download, you’re going to have to figure out where that invoice number is.
In my case it’s E5.
In some other invoices, it might be C3 or Z42.
Who knows?
Whatever that range is, you’re going to put that range there and there.
Also, a little bit more here.
I don’t want to just put the next invoice number in.
I want to clear out all of the stuff that I entered previously.
And for me, the salesperson and due date… I’m going to leave those like they were before.
But I want to clear out everything from A20 down to E39.
You see there’s formulas over here, I don’t want to clear out those.
So A20 to E39.
A20, colon, E39 dot ClearContents.
So that will clear any contents.
So now we have this macro that’s going to increment the invoice number.
Let’s figure out a way to run that.
The easiest way to run it is just to put any kind of a shape on the sheet.
Insert a shape.
You can choose whatever you like.
The circle, the star.
Oh, hey, let’s use the lightning bolt.
That’ll be fun.
So just draw a little lightning bolt over here.
And let’s see.
If you want to do the shape fill, feel free to do that.
But the most important part, right-click and say ‘Assign Macro’ and it’s going to be to our next invoice.
Alright, so now we put in someone bought five copies of Pivot Table Data Crunching at $34.95.
Alright, beautiful.
Now we want to clear that out and put the next invoice number in.
Click the lightning bolt.
Aha.
Next invoice and the stuff is cleared out.
So that’s good.
The other thing I really want to do here, is I want to save a copy of all of the previous invoices.
And there’s actually page out of MrExcel.com with this next little bit of code.
So I’m going to press Alt F11 and I just copy to the clipboard the code that I’ve written previously.
And what this does is it takes the active worksheet here and it copies it to a brand new workbook and then it creates a file name based on the invoice number.
So in my case, I’m going to ‘C:\aaa\’.
In your case, put the right folder and path there.
And then I want to start out with the letters Inv, the invoice number and dot XLSX.
‘ActiveWorkbook.SaveAs, the file name, and then I want to use an XLSX, that’s the macro-free version.
And then close that temporary version and then go back and run NextInvoice.
Okay.
So let’s go back and assign this to a shape.
So I’ll do Insert, Shapes.
And let’s call this… Actually I have a little text box here.
‘Save and Clear’.
Again, right-click, Assign Macro, SaveInWithNewName, click OK.
Alright.
Now, before we run this and make a copy, I need to make sure, it’s really important, that you save this workbook.
So that way you save the version with macros.
Alright.
So we have MrExcelInvoice and I’m going to Click ‘Save and Clear’.
Actually we’ll put just a little something here to test it.
‘Learn Excel from MrExcel’.
$39.95.
Alright, 23452.
I click ‘Save and Clear’.
Alright.
So you see that we’re now at the next invoice number, we’ve now cleared things out, and if I look in my recent file list, Invoice 23452 saved just a minute ago.
So a very simple way with these two tiny little macros here, about 10 lines of code, to take one of the invoices from Office Online, customize it, and have yourself a great little system to add the invoice number and also give you a way to save all previous invoices.
Well, hey.
I want to thank you for stopping by.
We’ll see you next time for another netcast of MrExcel.
Hey.
Welcome back to the MrExcel netcast.
I’m Bill Jelen from MrExcel.com.
The question sent in today is how do I generate the next invoice number?
I’ve set up an invoice.
How do I make it generate the next invoice?
Well, first of all, we are going to start with an invoice, that’s easy.
File, New.
Out here on Office Online they have all kinds of invoices to choose from.
I mean there’s folders and folders of these.
We’ll just choose one at random.
The concepts here are going to be similar, no matter which one you choose.
Alright.
So we’ve opened that from Office Online.
Obviously, some customisations here.
You want to go through and put your company name, address, and all that stuff.
But figure out where the invoice number is.
And I don’t like this.
They put the invoice number in square brackets.
That’s kind of weird.
I’m going to put 23451 so it’s actually numeric.
And I’m going to remember that that is cell E5.
Okay, now.
I’ve pulled this down and I need to save it. Now right now it’s in compatibility mode so it’s actually… Will work for Excel 2003.
I’m going to do File, Save As, save this on my local machine and make sure that it’s a Macro-Enabled Workbook.
If you usually save things as Excel workbooks, you need to go Macro-Enabled.
Alright.
We’ll just call it ‘MrExcelInvoice’.
Cool.
Alright.
So now we have a local copy saved and macros are ‘legal’.
Now, to get over to the macro language, we’re going to do Alt F11.
Don’t be afraid of this.
It’s just a tiny bit of code.
If you’ve never been to the macro language before, it opens up to this big grey screen.
View, Project Explorer.
In the Project Explorer, find the project called whatever you’ve saved it as.
And then we’re going to insert a module.
Insert a Module.
We’re going to create two 2 macros.
‘Sub NextInvoice()’.
Alright.
And what we do is we say, ‘Range (“E5”).Value = Range(“E5”).Value + 1’.
Right, now, whichever invoice you download, you’re going to have to figure out where that invoice number is.
In my case it’s E5.
In some other invoices, it might be C3 or Z42.
Who knows?
Whatever that range is, you’re going to put that range there and there.
Also, a little bit more here.
I don’t want to just put the next invoice number in.
I want to clear out all of the stuff that I entered previously.
And for me, the salesperson and due date… I’m going to leave those like they were before.
But I want to clear out everything from A20 down to E39.
You see there’s formulas over here, I don’t want to clear out those.
So A20 to E39.
A20, colon, E39 dot ClearContents.
So that will clear any contents.
So now we have this macro that’s going to increment the invoice number.
Let’s figure out a way to run that.
The easiest way to run it is just to put any kind of a shape on the sheet.
Insert a shape.
You can choose whatever you like.
The circle, the star.
Oh, hey, let’s use the lightning bolt.
That’ll be fun.
So just draw a little lightning bolt over here.
And let’s see.
If you want to do the shape fill, feel free to do that.
But the most important part, right-click and say ‘Assign Macro’ and it’s going to be to our next invoice.
Alright, so now we put in someone bought five copies of Pivot Table Data Crunching at $34.95.
Alright, beautiful.
Now we want to clear that out and put the next invoice number in.
Click the lightning bolt.
Aha.
Next invoice and the stuff is cleared out.
So that’s good.
The other thing I really want to do here, is I want to save a copy of all of the previous invoices.
And there’s actually page out of MrExcel.com with this next little bit of code.
So I’m going to press Alt F11 and I just copy to the clipboard the code that I’ve written previously.
And what this does is it takes the active worksheet here and it copies it to a brand new workbook and then it creates a file name based on the invoice number.
So in my case, I’m going to ‘C:\aaa\’.
In your case, put the right folder and path there.
And then I want to start out with the letters Inv, the invoice number and dot XLSX.
‘ActiveWorkbook.SaveAs, the file name, and then I want to use an XLSX, that’s the macro-free version.
And then close that temporary version and then go back and run NextInvoice.
Okay.
So let’s go back and assign this to a shape.
So I’ll do Insert, Shapes.
And let’s call this… Actually I have a little text box here.
‘Save and Clear’.
Again, right-click, Assign Macro, SaveInWithNewName, click OK.
Alright.
Now, before we run this and make a copy, I need to make sure, it’s really important, that you save this workbook.
So that way you save the version with macros.
Alright.
So we have MrExcelInvoice and I’m going to Click ‘Save and Clear’.
Actually we’ll put just a little something here to test it.
‘Learn Excel from MrExcel’.
$39.95.
Alright, 23452.
I click ‘Save and Clear’.
Alright.
So you see that we’re now at the next invoice number, we’ve now cleared things out, and if I look in my recent file list, Invoice 23452 saved just a minute ago.
So a very simple way with these two tiny little macros here, about 10 lines of code, to take one of the invoices from Office Online, customize it, and have yourself a great little system to add the invoice number and also give you a way to save all previous invoices.
Well, hey.
I want to thank you for stopping by.
We’ll see you next time for another netcast of MrExcel.
