Print a worksheet template 70 times with values from another works

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bill Harris - 15 Aug 2007 15:18 GMT

Hi
I am using Excel 2000.
I have created an input worksheet that contains information on about 70
people.
I have created another worksheet that will print out the information in a nice
format. This worksheet has been set up to pick up the required values for
the 1st person and print it out. That works very nicely.
What I would like to do is automatically have Excel continue to advance to
the information on the 2nd person print it out, 3rd person print it out, etc.
Here is an example of the input information
Last Name-First Name    -Title-# Employees-# Appraisal-# Excluded
Harris    Bill    Chief of Staff    200    170    25
Shmoe    Joe    Not Sure    5    5    0
Else    Somebody    Can't Remember    1    1    0
Here is an example of the printed template. Please note that it is much
nicer and more extensive than shown but it is just to give the idea.

2006/07 PERFORMANCE APPRAISAL

Name:    Bill Harris
Position:    Chief of Staff

Employees: 200
Employees Excluded:    25
Appraisal: 170
% Appraised: 97

Reply to this Message

JE McGimpsey - 15 Aug 2007 17:04 GMT

> I am using Excel 2000.

Just for info, you've posted to a MacXL newsgroup (hence the .mac. in
microsoft.public.mac.office.excel). But your question isn't platform
specific.

> I have created an input worksheet that contains information on about 70
> people.
[quoted text clipped - 3 lines]
> What I would like to do is automatically have Excel continue to advance to
> the information on the 2nd person print it out, 3rd person print it out, etc.

You don't say how you get that information to your "template" worksheet.
Do you use VLOOKUP()s? INDEX()s?

If the former, a simple way would be to use a something like this macro:

Public Sub CycleThroughRecords()
Dim ws As Worksheet
Dim rCell As Range
Dim rDest As Range

Set ws = Sheets("Template")
Set rDest = ws.Range("A1")
With Worksheets("Data")
For Each rCell In .Range("A1:A" & _
.Range("A" & .Rows.Count).End(xlUp).Row)
rDest.Value = rCell.Value
ws.PrintOut
Next rCell
End With
End Sub

The latter could be similar.

> Here is an example of the input information
> Last Name-First Name -Title-# Employees-# Appraisal-# Excluded
[quoted text clipped - 13 lines]
> Appraisal: 170
> % Appraised: 97

Reply to this Message

jpdphd - 15 Aug 2007 19:11 GMT

> Hi
> I am using Excel 2000.
[quoted text clipped - 22 lines]
> Appraisal: 170
> % Appraised: 97

Bill,
The Data Merge tool in Word is specifically designed to do what you
want to do. You can bring your Excel array into Word as a table, so no
re-writing necessary.
jpdphd

Reply to this Message

Mac Forum / Applications / Excel / August 2007

Print a worksheet template 70 times with values from another works