How to print invoices for each advertiser from Excel worksheet
 

I set up a worksheet which lists Publication Date, Account Rep, Advertiser,
Client ID, Columns, Inches, Price for newspaper advertisements. And what I
want to do is merge this information the day after publication into an
invoice format so that all that I have to do is command print invoices for a
certain day and Excel will print an individual invoice for each advertiser
listing the size of ad, color, and price. Thanks in advance for your help!

"Fred" wrote in message
...
I set up a worksheet which lists Publication Date, Account Rep,
Advertiser,
Client ID, Columns, Inches, Price for newspaper advertisements. And what
I
want to do is merge this information the day after publication into an
invoice format so that all that I have to do is command print invoices for
a
certain day and Excel will print an individual invoice for each advertiser
listing the size of ad, color, and price. Thanks in advance for your
help!

So the first thing you need to do is set up your invoice format. Many would
suggest designing your invoice in Word, then doing a mailmerge with the
Excel table as the datasource. But you can do your invoice as a separate
worksheet in the same workbook, then use a macro to send the values from a
job record (row) to the invoice sheet if required (ie: if publication date =
whatever).

In its simplest form, the macro to process a single job record would look
like:

Sub Do_Invoice()
Range("Invoice!B2") = ActiveCell
Range("Invoice!B4") = ActiveCell.Offset(0, 1)
Sheets("Invoice").PrintOut Copies:=1
End Sub

, ie: send the value from the current cell (pub_date) to B2 on Invoice, then
send the value from the next cell (zero rows down, one column across) to B4
on Invoice, then print Invoice.

From there, you'd just need a code loop to process all the records (if
pub_date = OK, run the code, move to next job record & do it again ; if not,
*just* move to next job record and do it again.

HTH,
Andy