• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 316
  • Last Modified:

Increment Excel cell every time a page prints



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:

Sub pri()
'
' pri Macro


xnum = InputBox("How many pages do you wish to print")
Range("e9").Activate
start_num = ActiveCell.Value
For i = start_num To xnum
    start_num = start_num + 1
    ActiveCell.Value = start_num
    Sleep 3000
Next i
End Sub

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?
thx...BobR
0
bobrossi56
Asked:
bobrossi56
1 Solution
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

If you've already printed 18 pages, then start_num will be 18. Now you want to print 8 pages, so xnum will be 8. Therefore, the for/next loop will be

for i = 18 to 8

This does not work, of course.

Since you always want to print as many pages as specified by xnum, you need to start your for/next loop at 1 and make sure that E9 will be incremented, for example like this:

xnum = InputBox("How many pages do you wish to print")
For i = 1 To xnum
    Range("E9") = Range("E9").Value + 1
    Sleep 3000
Next i

Open in new window


cheers, teylyn

0
 
bobrossi56Author Commented:
Works like a charm....thx much...BobR
0
 
Patrick MatthewsCommented:
bobrossi56,

Glad to see you got your answer!  Just one further note: if you are ever tempted to use the Workbook's BeforePrint event for this, be very careful: that event fires for print preview, so you may not get the behavior you desire :)

Patrick
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

Tackle projects and never again get stuck behind a technical roadblock.
Join Now