Learn Excel 2010 - "Next Invoice Number": Podcast #1505

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.

...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!

Category:

Howto & Style

Tags:

• Excel
• business
• accounting
• spreadsheets
• tutorial
• technology
• MrExcel
• Bill Jelen
• VBA
• Macro
• Invoice Number
• Generate Invoice Number
• Business
• Software
• Technology

[MrAnarhasanov]

Dear Bill,

I have a wokrbook which contains formulas from other workbooks (source files)

When I close those source files and run macro to save a copy to a specified folder all values where formulas existed show error in the saved file.

WHat adjustment should I made to the code?

The code is almost the save as yours.

[bjele123]

@MrAnarhasanov This sounds like a problem with Excel linking to closed workbooks. An Excel file can not read more than 10,000 cells from a closed workbook. It is easy to hit the 10K limit = VLOOKUP(A2,[ClosedWorkbook.xls­x]Sheet1!A1:J1001,2,False) is reading from 10010 cells. Can you save the workbook before closing the source files?

[MrAnarhasanov]

@bjele123 Thank you for answer, Bill. I save the file before closing source files.

The file contains several types of formulas (vlookup, sumproduct, dget etc.) I just realized that only cells which contain vlookup formulas don't return error. So I should try to change formulas to vlookup. OR is there any tip that I can adjust those other formulas in such a way that they don't return error?

[bjele123]

@MrAnarhasanov If VLOOKUP and SUMPRODUCT are working, then the issue is with DGET? I've never tried using DGET to an external workbook. There is an add-in floating around called "PULL" written by Harlan Grove. You might see if you can find this add-in in your search engine. (The add-in was stored at members.aol.com and was wiped out when AOL discontinued this service.) PULL would solve this problem.

[bjele123]

@VeritasAmantesVocat To print 200 blank invoices for completion by hand: If the invoice number in J5 & you wanted to print blank invoices 1001 through 1200.

1. Alt+F11

2. Insert, Module

3. Paste the following, without the numbered steps.

4. Sub PrintBlankInvoices()

5. For i = 1001 To 1200 : Range("J5").Value = i : ActiveSheet.PrintOut : Next

6. End Sub

7. Alt+Q to return to Excel

8. Alt+F8 to display the list of macros

9. Select the above macro and choose Run.

[MrAnarhasanov]

@bjele123 OK, thanks

[MrAnarhasanov]

@bjele123 Sumproduct also works fine.

[MrAnarhasanov]

Sumproduct also works fine.