Print data in each row on a seperate sheet template

C

Chris

In Access, you can create a form which views data from each row of the
database. When you print it automatically cycles through each row, printing
out identical sheets with just the data changing. Is it possible to do this
in excel without transferring the data to Access?
 
C

Chris

Apologies, I should have qualified my question more:

I have a sheet with a table of data. I have another sheet with an A4
template of a page design that I want printed - with company logos etc and I
want printed one page from the template for each row of data. Is it possible
for me to automate the creation of the printouts from the table and template,
within excel?
 
D

Dave Peterson

First, you could have used your excel table as the source and created the form
in MSWord (where you might have been able to make a nicer form???).

You may want to read some tips for mailmerge.
http://www.mvps.org/dmcritchie/excel/mailmerg.htm
http://www.mvps.org/word/FAQs/MailMerge

The first is from David McRitchie and the second is by Beth Melton and Dave
Rado.

======
But you can do what you want in excel (since your form is done).

But one thing I would do is add a new column A to your data. Use that as an
indicator that this row should be printed--if something happens and you have to
reprint a few, there's no sense printing all the forms.

This is the code I used:

Option Explicit
Option Base 0
Sub testme()
Dim FormWks As Worksheet
Dim DataWks As Worksheet
Dim myRng As Range
Dim myCell As Range
Dim iCtr As Long
Dim myAddresses As Variant

Set FormWks = Worksheets("form")
Set DataWks = Worksheets("data")

myAddresses = Array("b2", "c3", "d6", "F12")

With DataWks
Set myRng = .Range("b2", .Cells(.Rows.Count, "B").End(xlUp))
End With

For Each myCell In myRng.Cells
With myCell
If IsEmpty(.Offset(0, -1)) Then
'do nothing
Else
.Offset(0, -1).ClearContents 'for the next time
For iCtr = LBound(myAddresses) To UBound(myAddresses)
FormWks.Range(myAddresses(iCtr)).Value _
= myCell.Offset(0, iCtr).Value
Next iCtr
Application.Calculate 'just in case
FormWks.PrintOut preview:=True
End If
End With
Next myCell
End Sub

You'll want to change these lines:

Set FormWks = Worksheets("form")
Set DataWks = Worksheets("data")

myAddresses = Array("b2", "b3", "g6", "F12")

To match your workbook.

Since I used column A as the indicator, this will put the value in column B in
B2, column C in B3, D in G6 and E in F12. Just keep adding more addresses--but
go in that natural order (left to right starting in column B).

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Top