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

[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?

[MrAnarhasanov]

Sumproduct also works fine.

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

[MrAnarhasanov]

@bjele123 OK, thanks

[MrAnarhasanov]

@bjele123 Sumproduct also works fine.

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

[krn14242]

Thanks Bill.