Print ALL swimmers certificate

[Kieranz]

Hi All
I wish to printout certificates for all swimmers participating (50+).
On Sheet1 i have Name, Event, Result or time, Position ie 1st, 2nd etc.
besides other details.
On Sheet2 i have a Cert. template which has fixed details and also
variables (currently linked to sheet1 relevant cells) such as swimmer
Name, Event, indv Result, Position and also the winner's Name and
Result as a comparative. Of course the winner's own certificate will
not have the comparative. I can do this manually but to go thru
individually is such a time consuming and repeatative and monotonous.
Everybody is giving up on the idea.

Qn: How do i go about to print all the Certs in one go. I realise i
need a macro to loop thru all the names to somehow contact to the
template sheet putting in the variable then printout and then go to the
next name etc. but my mind is refusing to think; i am newbie on vba
though been trying over the few months using J Walk.. book and others
especially the web. Using Excel2003 with XP. Your help, ideas and
guidance will sincerely help. PS the gala is on 29/30 july.
Rgds
KZ

 

[NickHK]

Depending on the layout of your data, but something like:

dim SourceWS as worksheet
dim TemplateWS as worksheet
dim NumberSwimmers as long

set SourceWS = worksheet("Sheet1")
set TemplateWS = worksheet("Sheet1")

with SourceWS.Range("A1") 'Or where your data starts
For NumberSwimmers = 1 to .Currentregion.rows.count
TemplateWS.Range("SwimName").Value=.Offset(NumberSwimmers,0).Value
TemplateWS.Range("Position").Value=.Offset(NumberSwimmers,1).Value
'....etc, filling all data
'...
template.printout
next
end with

NickHK

P.S. Can I come the swim meet ?