Setting up pagination (2002 / rel 10)

Topic

New Reply

Several times a day I need to produce packing slips for tractor trailer loads of product shipped on pallets. There are usually 20 – 30+ packing slips for each load, one for each pallet, with one customer requiring two slips per pallet. The slips are numbered 1 of ___, 2 of ___, 3 of ___, etc. Is there a formula or macro that would make my job easier than manually entering the first number? (The “total” number is entered with a formula)

Reply | Quote

Replies

It’s probably possible to use formulas to compute the slip number, for example

=ROW()-3

or

=COUNTIF($B$1:$B1,$B1)

but it would help if we knew what your worksheet looks like – could you post a small sample workbook (with dummy data)?

Reply | Quote

I’ve never posted anything before; how do I do it?

Reply | Quote

Make sure that the workbook is less than 100 KB. If it’s too big, create a zip file containing the workbook.

Below the area where you type a post or reply, there is a box labeled “Attach a file – 100k Max.”, with a Browse… button next to it. Just before clicking Post It, click the Browse… button and select the workbook or zip file. It will appear as an attachment to your reply.

Reply | Quote

Here’s the file – hope it worked!

Reply | Quote

Thanks – the attachment worked. The situation is different from what I thought it was. You can use the following macro to print out slips; the macro looks at the value calculated in cell E28 and fills cell C28 with values 1, 2, …, etc. up to that value, and prints out the sheet for each value. You can specify multiple copies of each to be printed if you wish.

Sub PrintSlips()
Dim i As Integer
Dim n As Integer
n = Range("E28")
For i = 1 To n
Range("C28") = i
ActiveSheet.PrintOut Copies:=1 ' change if needed
Next i
End Sub

Reply | Quote

Thanks! I’ll let you know how it works.

Reply | Quote

This worked great!! You people at Wood’s Lounge are batting 1000 – never been disappointed when looking for an answer. Can’t thank you enough!!

Reply | Quote

You’re welcome! I’m glad it helped.

Reply | Quote

Okay, you did a great job with pagination for one set of numbers. What if I have two pallet sheets on one page? The forms are identical, so the numbering would be identical. I’ve attached the form I use so you can see what I mean. Thanks again, in advance!

Reply | Quote

You can replace

Range(“C28”) = i

with

Range(“C22”) = i
Range(“C49”) = i

Reply | Quote

Guess I didn’t explain myself so well this time. I meant to say I needed to have the forms number consecutively. The top form on the sheet should be 1 of N; the bottom form on the sheet should be 2 of N, etc. Thanks!

Reply | Quote

Try this version then:


Sub PrintSlips()
Dim i As Integer
Dim n As Integer
n = Range("E22")
For i = 1 To n Step 2
Range("C22") = i
Range("C49") = i + 1
ActiveSheet.PrintOut Copies:=1 ' change if needed
Next i
End Sub

The value in cell E22 should be even (divisible by 2).

Reply | Quote

OK! You did it again! Works great! You’re saving me LOTS of time!

Reply | Quote

OK, I’m back again. The pagination is working well with the two sheets on one page. Now I’m wondering if there is a way to have the top sheet number for the first half of the number of sheets needed and the bottom sheet number the last half? That would save me collating after they’re printed. Thanks!

Reply | Quote

I think this will do what you want:

Sub PrintSlips()
Dim i As Integer
Dim n As Integer
n = Range("E22")
For i = 1 To n / 2
Range("C22") = i
Range("C49") = n / 2 + i
ActiveSheet.PrintOut Copies:=1 ' change if needed
Next i
End Sub

Reply | Quote

It surely DOES do what I want. Again, I can’t thank you enough for all your help EVERY time I log in. God bless you!

Reply | Quote

This is a very interesting use of VBA and I have learned a great deal. One question though. If a person does not buy an even number (E22), how can you get the lower portion to print the correct number? For example if the client buys 57 items the top portion prints 1 of 57, the lower portion 29.5 of 57. Is the only alternative to make sure the client buys an even number

Reply | Quote

You could change the code as follows:

Sub PrintSlips()
Dim i As Integer
Dim n As Integer
n = Range("E22")
For i = 1 To (n + 1) 2
Range("C22") = i
Range("C49") = (n + 1) 2 + i
ActiveSheet.PrintOut Copies:=1 ' change if needed
Next i
End Sub

This will work for odd and even numbers. But because the total number of half pages is always even, you’ll get a superfluous last half page if the number in E22 is odd. You can simply discard that one.

Reply | Quote

Okay, this macro worked great – up until today. If I specify more than one copy to be printed, I get an error on the second sheet as follows:

PCL XL error
Subsystem: KERNEL
Error: IllegalOperatorSequence
Operator: EndChar
Position: 47

I have not made any changes to any of the files I set up using this macro. I edited the macro in one or two of the other files, changing the copies desired to “2” just to see if it was a problem with just one file. Any time I changed the copies desired to more than one, I got the above error message printed on the second page of the print out. Please advise. Thank you.

Reply | Quote

The error message that you mention is a printer driver error (in fact, a PCL 6 error), not an Excel error. See if there’s an updated driver for your printer. If not, you might check whether there’s a PCL 5 or PostScript driver for your printer – PCL 5 and PostScript are less “sensitive” then PCL 6.

Reply | Quote

Sorry for the late reply – You were right (again, as usual) about the problem being with the print driver. We installed PCL 5 and everything’s back to normal. Thanks again!

Reply | Quote