Increment Excel cell every time a page prints
Posted on 2011-09-24
Excel 2010 - My goal is to increment cell E9 by 1 (on my spreadsheet) for every page printed, so I wrapped the print command around a loop that increments cell E9 by 1 for every page I print. I get prompted when I run the macro for how many pages I want, then off it goes. I had to add a sleep to it as it was flooding my printers queue. Here is what I came up with:
' pri Macro
xnum = InputBox("How many pages do you wish to print")
start_num = ActiveCell.Value
For i = start_num To xnum
start_num = start_num + 1
ActiveCell.Value = start_num
This macro works great, I put a 1 in cell E9, run the macro, tell it I want tp print 25 pages and off it goes, gives me 25 pages, and on each page cell E9 is one number larger. Perfect...HOWEVER, if the printer jams at say page 17, and I clear the jam and I put an 18 in cell E9, run the macro and tell it I want 8 pages, the macro will not produce any pages. I get no error, I just get nothing out of the printer. I know the jam is cleared as I can print from Word fine. The macro only seems to work if I start with a 1 in E9. If I try to start E9 with any other #, the macro produces no pages.
Any ideas experts?