incremental printing in excel 2010

Hello,
I'm trying to print X number of pages with incrementing numbers in TWO cells per page.

Using the code below, i can easily increment one cell per printed page, but i need two.

Example:
page 1 should have increments 1 and 2
page 2 should have increments 3 and 4
and so on.

In my testing to this point, i can get page 1 with increments 1 and 2, but page two would have increments 2 and 3, then page three would have increments 3 and 4...

The important cells in Sheet1 are:
B8 = should be the first increment per page
B20 = should be the second increment per page
M11 = user defined number of pages to print

code i have so far follows

also, when i set B20 to "=B8+1" it doesn't seem to matter because B8 is always sequentially 1..2..3...4..5...

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim i As Long
Cancel = True
Application.EnableEvents = False
Application.Dialogs(xlDialogPrinterSetup).Show
With Sheets("Sheet1")
    For i = 1 To .Range("M11").Value
        .Range("B8").Value = .Range("B8").Value + 1
        .Range("B20").Value = .Range("B8").Value + 2     'playing with this line to see what happens
        .PrintOut
    Next i
End With
Application.EnableEvents = True
End Sub

Open in new window

LVL 2
FocISAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

FocISAuthor Commented:
just thinking out loud...  is there a way to either:

a. make the increment FOR loop count by two's (so the resulting math for B20 works)
b. set B20 = "B8+1" and make sure B20 has a chance to update before the print function
c. nest some more FOR loops between printing, that variable starting at one more than "i"
0
Saurabh Singh TeotiaCommented:
I'm not sure what exactly you are trying to do here.. But here are couple of your answer..

1. To increase for loop with incremantal of 2... You can do something like this..

 for i = 1 to 1 To .Range("M11").Value  Step 2

Open in new window


This will now increase on incremental of 2 which is 1,3,5,7 and so on...

2. To force excel to calculate before print you can add this line right below the calculation line...

Application.Calculate
DoEvents

Open in new window


This code will go to print only when calculation is complete and it does events...

Also not sure what you are looking for C one..

Saurabh...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FocISAuthor Commented:
This is exactly what i needed

The final code i used is:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim i As Long
Cancel = True
Application.EnableEvents = False
Application.Dialogs(xlDialogPrinterSetup).Show
With Sheets("Sheet1")
    For i = 1 To .Range("M11").Value Step 2
        .Range("B8").Value = i
        .Range("B20").Value = .Range("B8").Value + 1
        Application.Calculate
        DoEvents
        .PrintOut
    Next i
End With
Application.EnableEvents = True
End Sub

Open in new window

0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Saurabh Singh TeotiaCommented:
Okay Great... Always Happy to help.. :-)

Saurabh...
0
FocISAuthor Commented:
Just a follow up to this, and i can open a new question with points to reward if necessary but... based on the code above here's what the end user experiences

choosing to print 50 pages, the printer actually outputs in a random order

so they might get on paper in this order:
1+2
3-10
15-20
11-14
31-36
21-30
31-50

is there anything i can add in there to ensure each print job goes in order?

maybe a delay would do it, or something more specific?
0
Saurabh Singh TeotiaCommented:
Sure feel free to do so and if you can explain what exactly you are looking for. I can help you write the code for it..

Saurabh...
0
FocISAuthor Commented:
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.