Macro with using relative references

[SHAWKINS01]

Well it may not be MACRO but something else, but you are all the experts that have been helping me for a while now...

I am helping out a project here and am looking to see if this is possible to do.

We are starting to offer training classes and are providing certificates of completion for each attendee. Sometimes we have small classes (10-12 people) and other times we have 50 people. Other times we may have multiple training classes going on and want to print all the certs at one time. Currently the certificates are being filled out manually, which IMO is a complete waste of time. I prefer the Scrooge McDuck method (work smarter not harder) so i need to see if there is an easier way of adding additional certs to this file without having to manually go in and put the formulas in on each sheet.

I set up a template on page 2, so that we dont have to have different templates made up. I only have 5 fields I need to populate on each certificate, so I was wondering if there is an easy way to create the additional 49 templates and have them point to the correct fields without having to clean it up manually?

Does that make sense?

[zbor]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[protonLeah]

Please Login or Register  to view this content.

[SHAWKINS01]

That is excellent!

Is there a way to have it create these into the spreadsheet first before it starts printing? I know I am probably not asking the correct way how to do this...

I am looking for it to create a new sheet with the template showing the completed cert before it prints. Or, better yet is there an easy way to have it just duplicate the cert page as page 3, page 4, page 5 etc... keeping the formulas intact?

[protonLeah]

Please Login or Register  to view this content.

[SHAWKINS01]

PERFECT!!!! I snipped the following:
'print all sheets
ThisWorkbook.Worksheets(PrintList).PrintOut

'clear the certificate template
Sheets("Certificate").Range("D19,D21,D24,D26,D29").ClearContents

since it waned to send straight to the printer before I could review the certs. But after that it was Exactly what I wanted!!!