Hi All, Next to my main job, I'm also the IT-Backup when the IT-manager is on holiday in my company. Next to the usual Terminal Server, Internet Connection and email problems I also give some software assistance when needed. This is mainly on custom software for our sector (heatset printing), occasionaly however I get a office-application question. Most of the problems are fairly simply, but this week a collegue wanted something in Excel and I can't find a solution for him, so I'm asking the bit-tech community for a little help! The guy in question works in the shipping department and part of his job is to create all the shipping documents for each pallet/box. In a normal job these documents come out of our custom software, but on occasion he has to make them manually. He has to manually create/adjuste each document for each pallet.. wich is a pain as usually only the pallet number changes. Here is the problem: Let's say a job has 3820 boxes. Each pallet can hold a max of 50 boxes. So that makes 76 pallets with 50 boxes and one with 20 boxes. He will then take a standardfile and adjust it with the client name, delivery adress, job name, total quantity of boxes, quantity of boxes per pallet. Then he will add the pallet info (size of pallet, quantity boxes and the palletnumber). The problem is he has to manually adjust the pallet number (1/77, 2/77, 3/77, ... ,77/77) and the quantity (all 50, except the last one (20)). And print each one. How can I make excel print the document x-times (77 , or 3820/50 (rounded up))? And having it change the pallet number each time (1/77 - 77/77) and the quantity (1-76=50 , 77=20). And this automatically when printing the document. The total quantity of boxes, quantity per pallet, number of pallets is different for each job (variables). All help is VERY VERY welcome. I've been searching like hell for this. Kind regards, Michiel
Some one might have a better solution but.. you could use some VBA to make the change and order the print in a loop Code: For a = 1 to 77 ChangeCell = a & "/77" worksheet.print next a Caution is air code and will need to tested!
Hi, Thanks for your reply. I'm pretty good with Excel in itself, but have no experience with VBA. That does look more or less what I need though. It will need to get its information out of the excel sheet(cell) first (77) as this is different for each job. And it will need to export the variable value (x/77) to a certain cell aswell. Could anybody post a correct code for this? And a short explanation on how I get it into Excel and how to execute it? Thanks very much, Michiel
Hi again! With the macro recording, I figured out how the VBA works, I adjusted your suggestion and .. it works!! Thanks! I think I'll ask if I can follow a VBA course . Michiel
Pleased to help, donations in the tin at the door VBA is great for this kind of stuff... i'm mostly self taught, pretty much muddled through at first by following code from others in the dept and online examples. But i think a course for VBA and advanced office could be very useful if you come accross this kind of thing often.
