Certificate Printing Macro

[Laurenwsjassoc]

Hello,

I need to figure out how to write a macro that will take info from a worksheet (a row that goes across many columns) and place that info in another worksheet that is a certificate template. This macro also needs to recognize when it hits the last filled in column in a row it should print the certificate, move to the next row replace the info in the cert with the date in the next row and then print and keep doing that until it gets to an empty row.

Is this sort of macro possible? If so how do I go about creating it. I know it seems confusing I am happy to answer any questions to clarify.

Thank you!

 

[MrKowz]

Welcome to the forums!

This is a relatively simple macro, but it requires a lot of details:

• What does each column map to in the certificate?
• What is the name of the sheet with the data, and what is the name of the sheet with the certificate?
• What row/column does your data start in?

 

[Laurenwsjassoc]

• What does each column map to in the certificate? They are certificates for employee 401(k) data, ie: derferral amounts, matching amounts and loan amounts, account totals etc. These are yearly summary certs
• What is the name of the sheet with the data, and what is the name of the sheet with the certificate? The sheet with the data is named data, the worksheet with the certificates is named "certificate"(so original, I know)
• What row/column does your data start in? The data starts in column one row 12 (the first item is the employee name)

I hope this helps clarify what i am trying to do. thanks!

 

[MrKowz]

To elaborate a bit on the first point, what cells does each column map to on the certificate? Excel needs to know exactly where to place these values.

For example:

• Col A goes into C18
• Col B goes into D18
• Col C goes indo C21
• etc...

 

[Laurenwsjassoc]

This is a little confusing for me to answer but for example Info from the "data worksheet" in in column I12 would go into the "certificate" worksheet into col B18. But it is very intricate I can show you the formula we used from a lotus spreadsheet. I work in an old school company and I am trying to get them int 2011 and using excel, but this is all very over my head as far as excel goes...I pasted it below:

<!--This file was created using Lotus 1-2-3 on 05/16/11 at 02:50:13 PM-->*******>****** name=Generator content="Lotus 1-2-3">*******>****** name=Version content=1.0>*******><!--StartFragment--><BASEFONT color=#000000 size=2 face=Arial LOTUS_SIZE="12"></BASEFONT><TABLE border=0 LOTUS_ANCHOR="D35..H76"><LOTUS_GLOBAL GRIDCOLOR="#c0c0c0" GRID="ON" WIDTH="81" HEIGHT="16" LOTUS_FORMAT="General" VALIGN="BOTTOM"></LOTUS_GLOBAL><LOTUS_RANGENAME NAME="\A" RANGE="D35"></LOTUS_RANGENAME><LOTUS_RANGENAME NAME="\N" RANGE="G35"></LOTUS_RANGENAME><LOTUS_RANGENAME NAME="\ONE" RANGE="D35"></LOTUS_RANGENAME><LOTUS_RANGENAME NAME="\W" RANGE="D41"></LOTUS_RANGENAME><LOTUS_BLANKCOL WIDTH="108" COLUMN="4"></LOTUS_BLANKCOL><LOTUS_BLANKCOL WIDTH="108" COLUMN="7"></LOTUS_BLANKCOL><TBODY><TR><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">{BREAKON}</TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=63 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">\n</TD><TD bgColor=#ffffff height=16 vAlign=bottom colSpan=2 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">{RANGE-TRANSPOSE b:a3;}</TD></TR><TR><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">{PANELON}</TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=63 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">{down 1}</TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD></TR><TR><TD bgColor=#ffffff height=16 vAlign=bottom colSpan=2 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">{WINDOWSOFF}</TD><TD bgColor=#ffffff height=16 vAlign=bottom width=63 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom colSpan=2 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">{RANGE-TRANSPOSE b:a4;}</TD></TR><TR><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">{GOTO cert}</TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=63 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD></TR><TR><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=left LOTUS_CELLTYPE="LABEL">{branch \w}</TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=63 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">{down 1}</TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD></TR><TR><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=63 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom colSpan=2 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">{RANGE-TRANSPOSE b:a5;}</TD></TR><TR><TD bgColor=#ffffff height=16 vAlign=bottom colSpan=2 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">{RANGE-TRANSPOSE b:a8;}</TD><TD bgColor=#ffffff height=16 vAlign=bottom width=63 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">Name</TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD></TR><TR><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">{right 6}</TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=63 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD></TR><TR><TD bgColor=#ffffff height=16 vAlign=bottom colSpan=2 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">{RANGE-TRANSPOSE b:b18;}</TD><TD bgColor=#ffffff height=16 vAlign=bottom width=63 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">D-open</TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD></TR><TR><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">{Left 4}</TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=63 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD></TR><TR><TD bgColor=#ffffff height=16 vAlign=bottom colSpan=2 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">{RANGE-TRANSPOSE b:c18;}</TD><TD bgColor=#ffffff height=16 vAlign=bottom width=63 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">Deferral</TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD></TR><TR><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">{right 5}</TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=63 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD></TR><TR><TD bgColor=#ffffff height=16 vAlign=bottom colSpan=2 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">{RANGE-TRANSPOSE b:d18;}</TD><TD bgColor=#ffffff height=16 vAlign=bottom width=63 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">D-Gains</TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD></TR><TR><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">{right 2}</TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=63 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD></TR><TR><TD bgColor=#ffffff height=16 vAlign=bottom colSpan=2 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">{RANGE-TRANSPOSE b:e18;}</TD><TD bgColor=#ffffff height=16 vAlign=bottom width=63 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">D-Dist</TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD></TR><TR><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL" LOTUS_PROTECT="OFF">{right 5}</TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=63 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD></TR><TR><TD bgColor=#ffffff height=16 vAlign=bottom colSpan=2 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">{RANGE-TRANSPOSE b:b20;}</TD><TD bgColor=#ffffff height=16 vAlign=bottom width=63 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">SH-open</TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD></TR><TR><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">{left 11}</TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=63 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD></TR><TR><TD bgColor=#ffffff height=16 vAlign=bottom colSpan=2 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">{RANGE-TRANSPOSE b:c20;}</TD><TD bgColor=#ffffff height=16 vAlign=bottom width=63 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">SH</TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD></TR><TR><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">{right 12}</TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=63 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD></TR><TR><TD bgColor=#ffffff height=16 vAlign=bottom colSpan=2 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">{RANGE-TRANSPOSE b:d20;}</TD><TD bgColor=#ffffff height=16 vAlign=bottom colSpan=2 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">SH-Gains</TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD></TR><TR><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">{right 2}</TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=63 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD></TR><TR><TD bgColor=#ffffff height=16 vAlign=bottom colSpan=2 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">{RANGE-TRANSPOSE b:e20}</TD><TD bgColor=#ffffff height=16 vAlign=bottom width=63 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">SH-Dist</TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD></TR><TR><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">{right 5}</TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=63 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD></TR><TR><TD bgColor=#ffffff height=16 vAlign=bottom colSpan=2 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">{RANGE-TRANSPOSE b:b22}</TD><TD bgColor=#ffffff height=16 vAlign=bottom colSpan=2 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">Mtch-Open</TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD></TR><TR><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">{left 24}</TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=63 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD></TR><TR><TD bgColor=#ffffff height=16 vAlign=bottom colSpan=2 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">{RANGE-TRANSPOSE b:c22;}</TD><TD bgColor=#ffffff height=16 vAlign=bottom width=63 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">Match</TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD></TR><TR><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">{right 1}</TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=63 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD></TR><TR><TD bgColor=#ffffff height=16 vAlign=bottom colSpan=2 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">{RANGE-TRANSPOSE b:g22;}</TD><TD bgColor=#ffffff height=16 vAlign=bottom colSpan=2 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">Mtch-Vest</TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD></TR><TR><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">{right 24}</TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=63 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD></TR><TR><TD bgColor=#ffffff height=16 vAlign=bottom colSpan=2 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">{RANGE-TRANSPOSE b:d22;}</TD><TD bgColor=#ffffff height=16 vAlign=bottom colSpan=2 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">Mtch-Gains</TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD></TR><TR><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">{right 8}</TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=63 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD></TR><TR><TD bgColor=#ffffff height=16 vAlign=bottom colSpan=2 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">{RANGE-TRANSPOSE b:a24}</TD><TD bgColor=#ffffff height=16 vAlign=bottom colSpan=2 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">Loan Footer</TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD></TR><TR><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">{right 1}</TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=63 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD></TR><TR><TD bgColor=#ffffff height=16 vAlign=bottom colSpan=2 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">{RANGE-TRANSPOSE b:f24}</TD><TD bgColor=#ffffff height=16 vAlign=bottom colSpan=2 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">Loan Balance</TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD></TR><TR><TD bgColor=#ffffff height=16 vAlign=bottom colSpan=2 align=left LOTUS_CELLTYPE="LABEL">{Print} certificate</TD><TD bgColor=#ffffff height=16 vAlign=bottom width=63 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD></TR><TR><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">{insert-rows ;1}</TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=63 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD></TR><TR><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">{end}</TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=63 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD></TR><TR><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=left LOTUS_CELLTYPE="LABEL">{left}</TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=63 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD></TR><TR><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">{delete-rows}</TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=63 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD></TR><TR><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=left LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="LABEL">{down 1}</TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=63 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD></TR><TR><TD bgColor=#ffffff height=16 vAlign=bottom colSpan=3 align=left LOTUS_CELLTYPE="LABEL">{if @cellpointer("type")="L"}{branch \w}</TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD><TD bgColor=#ffffff height=16 vAlign=bottom width=108 align=right LOTUS_FORMAT="Comma;2" LOTUS_CELLTYPE="BLANK"> </TD></TR></TBODY></TABLE><!--EndFragment-->


The words you see such as "loan balance", SH, "vesting" are column titles in the data worksheet. I am sorry for all this mess! But thank you for trying

 

[MrKowz]

This is a little confusing for me to answer but for example Info from the "data worksheet" in in column I12 would go into the "certificate" worksheet into col B18.

I have 0 knowledge of Lotus coding, however what you said initially is what I am looking for, but for each item to copy to the certificate. You say that I12 would go into B18 on the certificate. What about A12, B12, C12, D12, etc... all the way to the last column of your data? What cells do they go into on the certificate worksheet?

 

[Laurenwsjassoc]

I have 0 knowledge of Lotus coding, however what you said initially is what I am looking for, but for each item to copy to the certificate. You say that I12 would go into B18 on the certificate. What about A12, B12, C12, D12, etc... all the way to the last column of your data? What cells do they go into on the certificate worksheet?

Ok I understand what you are getting at. It is a lot of info so it is going to take me a while to write it out....I will respond tomorrow morning and hopefully you will still be interested in working with me....thank you for what you have already done!

 

[MrKowz]

I have 0 knowledge of Lotus coding, however what you said initially is what I am looking for, but for each item to copy to the certificate. You say that I12 would go into B18 on the certificate. What about A12, B12, C12, D12, etc... all the way to the last column of your data? What cells do they go into on the certificate worksheet?

Ok I understand what you are getting at. It is a lot of info so it is going to take me a while to write it out....I will respond tomorrow morning and hopefully you will still be interested in working with me....thank you for what you have already done!

Not a problem, just reply back in this topic with the added information, and I will try to work it all into a macro for you.

Cheers!

 

[Laurenwsjassoc]

Ok I have all of the items for the simplest cert that I need to create. I have listed each item from the "cert" that needs to be filled in and next to that is the cell from the "data" that should be pulled into the cert. I hope this is what you were looking for:

"Cert" "Data"
A8 A12
B18 B12
B22 W12
C18 C12
C22 E12
D18 H12
D22 X12
E18 K12
E22 Z12
F18 I12
F22 Y12
G22 F12

So the First cell in cert is the employee name. The subsequent cells are the data specific to that employee. Once all of that info is complete the macro would recognize to print the cert and move to the next employee(the next row of employee data) and replace the cert with that data print it and so on. Once a blank row was hit the macro would know to stop.

I hope this helps. even if we can't figure out that exact macro maybe you could point me in the right direction on how to get going or what resources I can use to get this done in excel

Thank you!

 

[Ruddles]

MrKowz, I'm sure you have this more than adequately covered but how about this for a possible solution...

The user creates his certificate template using the INDIRECT function to refer to the cells from the data sheet based on a row number somewhere in the template sheet - for example, he puts a row number in cell Z1 of the template and in cell A8 of the template he'd have =INDIRECT("DataSheet!A"&Z1), in cell B22 of the template he'd have =INDIRECT("DataSheet!W"&Z1), etc.

When he puts a 1 in Z1 (manually), the template will populate itself from row 1 of the data sheet; when he puts a 2 in Z1, the template populates itself from row 2 of the data table, etc.

Then the macro just needs to cycle through the data table, placing each row number in turn into Z1 and printing the template.

That way you don't need to shift any data using VBA - it's all done by worksheet formulae. The user doesn't have to tell you where the data comes from or where it has to go (because he does all of that) and he can play around with the template without having to dip into VBA.

Did I explain myself clearly?

What do you reckon?