View Full Version : Solved: Macro sends sheets to printer in ascending order, but they're not printed so.

06-03-2005, 07:44 AM

I have two sheets and they work just like this...

Sheet1 has a table with a database. Each line is a record, of course, and one of its columns is used for RANKING. The table is sorted by this ranking column, in ascending order, from 1 to the last classified number (which is not fixed, for it depends on the number of records in the table).

Sheet2 is a kind of CERTIFICATE. This certificate is printed for half of the total number of records in Sheet1. So, if Sheet1 has 50 records, for example, there will be 25 printed certificates for the first 25 classified records.

Sheet2 is full of fields with VLOOKUP functions related to one single cell called PLACE (A23). This cell, of course, is the PLACE of the record in the ranking. When I type a number in this cell, the VLOOKUP functions complete all fields with the record data from the table in Sheet1, related to that typed number (ranking).

So, these are the procedures I used to do to print the certificates...

- Type 1 in the cell PLACE (for the first classified record);
- The VLOOKUP functions complete the fields of the certificate based on number 1;
- Then I pressed CTRL+P and send it to printer;
- Type 2 in the cell PLACE (for the second classified record);
- The VLOOKUP functions complete the fields of the certificate based on number 2;
- Then I pressed CTRL+P and send it to printer;
- Type 3 in the cell PLACE (for the third classified record);
- The VLOOKUP functions complete the fields of the certificate based on number 3;
- Then I pressed CTRL+P and send it to printer;
- And so on...

I used to do this manually to print all certificates. Then, I created a macro for Sheet2 which works very well. Here it is...

Sub PrintCertificates()

Dim i As Long

If MsgBox("Have you already chosen the printer?", vbYesNo, "Print alert") = vbNo Then
Exit Sub

i = ActiveCell.Value
While i <= Sheets("Sheet1").Range("BK1").Value
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
ActiveCell.Value = ActiveCell.Value + 1
i = ActiveCell.Value
End If
End Sub

- A23 is the cell in Sheet2 (certificate) for the PLACE.
- BK1 is the cell in Sheet1 (database) which has the half number of records. In other words, BK1 has the total number of printable certificates (for example, if there are 40 records, BK1 value is 20).

So the macro works like this (steps)...

1) A23 starts from 1 (for it's already typed there);
2) The macro sends Sheet2 to printer;
3) A23 receives its own value plus 1;
4) The macro checks if A23 value is now higher than Sheet1.BK1 value;
5) If not, the macro sends Sheet2 to printer and returns to step 3 (and so on);
6) When A23 value is higher than Sheet1.BK1, it stops printing.

That's it! It works extremely well! Now I don't have to print certificates manually, one by one anymore. But there's one point...

The macro sends the certificates to printer in ascending order of place. That's natural, for it's always A23 value plus 1, starting from 1. But the printer doesn't print it in order. For example, it has to print 20 certificates, then it prints something like this...

Order of printed places:

2, 3, 4, 5, 7, 8, 9, 10, 12, 13, 14, 15, 17, 18, 19, 20, 1, 6, 11, 16

Here's my doubt...

If they started from 1 and were sent to printer one by one, in ascending order, why does that happen? Something related to the printer's buffer? Is there any way to fix it?

I use HP Business Inkjet 2280, local port.

The certificate is basically formated with black and grey colors. There's just a little bit of red and blue.

There are more sheets in the file besides these two I've mentioned. Depending on the number of records, the file may achieve 1400 or 1600 kb. Sometimes it's a big and heavy file (that's one of the reasons I use that "calculate" function in the code, after updating the VLOOKUP functions).

If anyone can help me, I appreciate it very much.

Thank you all for your attention. :)



Bob Phillips
06-03-2005, 07:58 AM
Try putting a wait in the loop.

06-03-2005, 09:14 AM

Hum... Cool! I'll try it. Let's see it... :)

Thanks, buddy. :thumb