printing a form
 

Is it possible to take a database of invoice information (i.e. each row
contains company name, invoice date, amount due, etc) and import(?) it into
a sheet that has an invoice form and print separate invoices for each
customer? I'd also like to have the option to print just those that are
current, or 30, 60, 90 or 120 days out. I'm hoping this can be a macro that
can do all that meet a certain criteria so I don't have to hit "print" for
each invoice. Is this possible? And, what would be the best way of doing
it?

Thanks in advance,
Gary

printing a form
 

Is the database in Excel already? If not you can import from an external
database; do a search of this site for "External Data" or "MSQuery."

But if the data is already in Excel, or you can import it using MSQuery:

You can set up a worksheet as your invoice form. To fill in the form you
can use various lookup formulas. For example, let's say you want the data
from row 25 (forget for now how to find the data, just assume that is the row
you need). In the cell of the invoice that has the company name the formula
could be:
=OFFSET(Sheet1!A1, 24, 0)
(this assumes your data is on Sheet1 with the company name in column A)
The invoice date (if it is in column B) would be
=OFFSET(Sheet1!A1, 24, 1)
etc.

I like using named ranges to make these easier to use, so call Sheet1!A1
something like HOMEBASE and have another cell (perhaps hidden or perhaps on
another sheet) for ROWNO. We will use ROWNO to store the row you want to
use, so the formulas now look like this:
=OFFSET(HOMEBASE, ROWNO-1, ...)

Finally, there are many ways to code a macro but the easiest one is to
simply increment the number in ROWNO in steps of 1. Read the data from your
list to see if you need to print it; if so do the print, if not step ahead.
Example:

Sub PrintInvoices()
Dim i as Integer
i=1
While Worksheets("Sheet1").Cells(i,1).Value < ""
' Put an IF statement here to look at the data and decide if it needs to
be printed
' Example - find invoice dates older than 30 days:
If Worksheets("Sheet1").Cells(i,2).Value < Date() - 30 Then
Range("ROWNO").Value = i
' Now your invoice formulas should be pointing to the current row, so:
Worksheets("Invoice").Printout
End If
Wend
End Sub

This is just the outline but hope you can take it from here.
--
- K Dales


"Michelle Beard sbcglobal.net" wrote:

Is it possible to take a database of invoice information (i.e. each row
contains company name, invoice date, amount due, etc) and import(?) it into
a sheet that has an invoice form and print separate invoices for each
customer? I'd also like to have the option to print just those that are
current, or 30, 60, 90 or 120 days out. I'm hoping this can be a macro that
can do all that meet a certain criteria so I don't have to hit "print" for
each invoice. Is this possible? And, what would be the best way of doing
it?

Thanks in advance,
Gary

printing a form
 

That looks great. It's not as "automated" as I would wish, but it's the
best idea I've seen/come up with so far. Is it possible to send the data
from the first row who's date is "X" to the invoice sheet and print then
step to the next row who's date is "X" and so on and so on? That way, I
could set up a macro to print all invoices that are 90 days out, or any
invoice that is 120 days out and so on.

Thanks for the help,

--
Gary Beard