Excel - entering data in one worksheet that is copied to several

[Bus429]

Hi All,
I've had lots of help here before so I'm anticipating success...
I'm running Excel 2007 and have a workbook I use to track the issue of certificates and use several sheets that contain templates for the various types of certificate issued.
I've worked out how to use =Sheet1!a10 to allow a cell value entered in Sheet 1 A10 to be copied to as many cells as I want in other worksheets. That has really helped.
However, Sheet 1 of the workbook contains columns of certificate numbers (so no duplicates), names, organisation, course reference and date of attendance.
I want to be able to enter these values in a row so that the data will be copied to the certificate template (doesn't matter if copied to all, I'll only print the one I need) allow me to print and then with every subsequent row I enter on Sheet 1, overwrite the data that was in the certificate from the previous row. Obviously, the data in Sheet 1 is unique and has to remain as a record but the cells on the certificate templates can be overwritten because I print to PDF every time I complete a row in Sheet 1. If I can add a button to a frozen pane on Sheet 1 that allows me to print the certificate I want, even better.
Is it possible?

[Rob82]

Hi bus,

I've done a bit of macro writing in excel before and it won't be a problem to do what you've asked, even the printing from a button bit.

I'll have a quick look now and see what i come up with.

[Bus429]

Thank you Rob

[Rob82]

Right here we go.

I think i've done what you asked but if it need changing in anyway just let me know.

I don't know if your familar with macros in excel, i'll write this as if you don't with step by step instructions.

1. Open the developer tab and select Visual Basic (you may need to go into excel options and select Show Developer tab in the Ribbon.)

2. In the left hand side of the visual basic page there will be a folder named Modules, left click this and select Insert and then Module.

3. Copy the text below into the white screen in the new module.

Sub Certificate()
Row = ActiveCell.Row
Sheet2.Range("A1") = Sheet1.Cells(Row, "A").Value
Sheet2.Range("A2") = Sheet1.Cells(Row, "B").Value
Sheet2.Range("A3") = Sheet1.Cells(Row, "C").Value
Sheet2.Range("A4") = Sheet1.Cells(Row, "D").Value
Sheet2.Range("A5") = Sheet1.Cells(Row, "E").Value
Sheet2.PrintOut
End Sub

You can change the Sheet2.Range("A1"), Sheet2.Range("A2"), etc. to the cells in the certificate that you need. i.e. Sheet2.Range("G16")

4. Now go to Sheet1 in the workbook and click the Developer Tab in the ribbon, then Insert, and then the Button under Form controls.

5. Click Where you want the button to be on the worksheet. If you freeze the top row of the worksheet then if the button is there it will always be available. When you place the button it will open a window to select a macro to associate with it select "Certificate" and OK.

All should then be working.

The way it works is whichever row the activecell is in, is the row of data that will be copied over and printed. So if you want to print the certificate on row 21 just select any cell in row 21 and click the button.

While your playing with it you can change the Sheet2.Printout to Sheet2.PrintPreview so you'll just get the print preview page rather than having to keep printing the pages.

Hope this helps. Let me know how it goes.

Rob

[Bus429]

Thanks Rob - I'll give it a go an get back to you.

Bus

[Bus429]

Hi Rob,
Got as far as opening Visual Basic but there is no folder bar with "Modules".

[Rob82]

Ah right sorry.

You see on the left where it says VBAProject (name of worksheet) and below that Microsoft Excel objects?

Just right click anywhere in that white box and select Insert and Module.