Batch Printing

athertm

New Member
Joined
Feb 16, 2002
Messages
26
I have a sheet that contains prices for goods depending on the customer number entered in to a particular cell. This is then printed off and sent to each customer. I would like to know how to get this cell to change automatically and print the document for each customer in turn (the customer numbers are held in a column on the sheet.)

Many thanks.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Anne Troy

MrExcel MVP
Joined
Feb 18, 2002
Messages
2,607
There are several options:

Make a mail merge to Word provide the automation for you:
http://www.theofficeexperts.com/word.htm#MailMerge

Use VBA to automate right from Excel, but there's a few questions...

You don't want all your customer's data going to every customer, right? So the values for each customer would have to be copied to a new sheet, and the links broken.

Do you want to send one worksheet, or the entire workbook?

Do you want to pick from your contact list, store the email address with your customer names, or just type an address into a box that pops up?
 

Jack in the UK

Well-known Member
Joined
Feb 16, 2002
Messages
3,215
Hi

I feel you want to run a program within Excel and then get a file / excel document for each customer IE independant.

MAIL MERGE Will never do thi sby its very difinition and also mail merges are not so good if there are many differen letters / documnets to produce, sounds like you have variables ie customer A will be differed products from customer B, Word will fall flat on its face on this one unless you really VAB her heavy heavy.

Excel however could be made to do this from a few cood VBA scripts linking your array table to the leter document, this will be in each document so no wat each customer see others details, Un Like word which is 100 letters one folled by the next and so on. this would give 100 docunents in Excel.

Advantages does as you want and also you can find them, mail merges are dreadful to find a letter if you have 500 a day and save the word doc for 3 years you have to find the doc then teh letter from ??? I dont know.

If you name the Exel doc uniquley then you can search for say Jack and all my docs pop up and look for teh date and BINGO!!

Anne Troy has gone for Mail Merge and Jack says stay in Excel self contain it. Anne offers good ideas and as such i would chase these and then decide what way to go, as im a bit mix from the above post whats best

Just showing the difference between Excel documents and mail merge both have their place

Jack
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi athertm:

Following up on the valuable suggestions from Dreamboat and Jack in UK, I present one common Excel solution through the following simulation ...
y021102h1.xls
ABCDEFGHIJK
1Jack12CustIDCustIndexNameCompanyPlace
2JackinUKMrExcel1MrExcelMrExcelConsultingOhio
3JackConsultingJack12JackinUKJackConsultingLondon
4LondonDream13AnnWordExpertPA
5
6InformationforInformationtotheleftiscustomizedforeachcustomerbyentering
7only1pieceofinformationincellA1
8JackinUKofJackConsultingthenalltheinformationisreadfromtheCustomerdatabaseabove
9
10
11
12
13
14
Sheet6
</SPAN>

I hope this helps.

Regards!
Yogi Anand
 

Jack in the UK

Well-known Member
Joined
Feb 16, 2002
Messages
3,215
Nice one Yogi!!!

This wll work without the problems and could if only a few save off to documents as needed

Poss to hide data in another sheet or have the print area as the part you want to post

Nice!!
 

athertm

New Member
Joined
Feb 16, 2002
Messages
26
Thanks for the responses everyone!

I've got to admit, it sounds very complicated. I thought it would be easy as maybe running some script that says.

1) put cell a2 (this is the 1st customer in the list) in to cell c7 (this is the cell that the lookups use to change prices/poducts etc)

2) Update/Print the sheet

3) add 1 to the row number for the customer list ie. a2 + 1 (don't laugh the last time i did any coding I was in shorts)

4) Goto no. 1

Over Simple?

I'm back in work tomorrow so maybe I can post the sheet.

Thanks again everyone!
 

Forum statistics

Threads
1,144,433
Messages
5,724,317
Members
422,543
Latest member
TimB_13

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top