steveg1964

Hi Guys,
I'm new here......so be gentle with me!!

I'm after a little help in creating macros in Excel to perform the following tasks:

I am making up a certificate that will have an individual number - this needs to increment each time the workbook is opened and saved, using the certificate number as addition to a standard file name

To summarise, the certificate number would be something like 2007 (cell G11) with the incrementing number in cell H11.

The saving part needs to be something like C:\My Documents\Work\Certificates\2007_xxxx.xls, wher xxxx will be the incremented number from the H11.

It may also be nice to create an index so each time a new certificate is created it writes a new entry.

Hope someone can point me in the right direction

Cheers
Steve

Glaswegian

Hi Steve and welcome to TSF.

Hopefully this will do what you need - it uses the Open Event so that the workbook is saved as soon as it is opened. Change the sheet names to suit and test first!
The code goes in ThisWorkbook Module.

steveg1964

Hi Iain,

Happy New Year

Thanks for the code - the incrementation works well.
However, i would like to modify it so I have a seperate location for the certificate listing.
How can I modify the code to open a new Workbook, named "Certificate Listing", that saves the list of certificate numbers, when I close the Certificate?

Hope this makes sense

Regards
Steve

Glaswegian

Hi Steve and a Happy New Year to you too.

Can you kindly clarify for me - when you say 'Certificate numbers' what number do you mean? And what do you mean by 'close the Certificate' - is that the first workbook? Sorry if I'm not quite understanding here but better to be sure before I start producing more code.

steveg1964

Hello Iain,

sorry, I should have explained a little clearer.....

Certificate number is the incrementing number from cell H11, along with the text in cell G11 (2007), ie when written to the Certificate list would read "path details\ 2007_xxxx" where xxxx is the number.
'Closing the Certificate' is as you say the Certificate workbook (I have actually changed it to a Template now, so it has the extension '.xlt'.
So, to summarise, I would like to open the Certificate Template, which will have the individual incremented identity number. Following this I would enter customer details etc and then have the file save to the location 'C:\Documents and Settings\Steve\My Documents\Work\Certificates'.
In addition to this, every time that the file is saved, an entry is written into the Certificate List (same directory).

Hope this is clearer.

Once again thanks for your assistance - I can understand you code once written, but I haven't got a clue where to start for myself.

Cheers
Steve

Glaswegian

Hi Steve

See if this works (untested but should be OK). I've defined a variable and set it to your Certificate workbook. The workbook is opened, the number value is written to the workbook, then the workbook is saved and closed. This is all done on the opening of the main workbook - if this is not suitable then please let me know. Remember to change sheet names as required etc. Once again, please test thoroughly!!
Hope I've understood correctly.

steveg1964

Hello Iain,

I have dug this one out of the depths of time and re-started it......

Thanks for the Macro you had previously written - it worked nicely. I have modofied things a little as I progressed....
The current code is as below, and creates an incrementing certificate number when the file is opened and also if I click the command button that I have added. In some respects it may be better to remove the increment on opening, but I can live with that as I may move the work sheet into several calibration templates at a later date and link them all back to the master. Additionally, the macro writes some details to a sheet called Cert_Listing and updates each time as above. However, what I am keen to do is move the Cert_Listing sheet to a new work book, named say C:\Documents and Settings\Steve\My Documents\Work\Certificates\Certificate Register.xls
I have tried but just can't get my head around it!!
Sorry the code is in this text box - I couldn't see a seperate code box!
Once again, thanks for your assistance

Steve

Code

Private Sub CommandButton1_Click()
'Macro to increment Certificate Number on opening of template

'Private Sub Workbook_Open()

Sheets("Cal_Cert").Range("J11").Activate
ActiveCell.Value = ActiveCell.Value + 10



ThisWorkbook.SaveAs "C:\Documents and Settings\Steve\My Documents\Work\Certificates\" & Sheets("Cal_Cert").Range("G11").Value & "_" _
& Sheets("Cal_Cert").Range("J11").Value & ".xls"

'To create a list of all Certificates generated with Customer, Ref, P/O & Issue Date

Sheets("Cert_List").Range("A65536").End(xlUp).Offset(1, 0).Value = ThisWorkbook.Name
Sheets("Cert_List").Range("A65536").End(xlUp).Offset(0, 1).Value = Sheets("Cal_Cert").Range("B11").Value
Sheets("Cert_List").Range("A65536").End(xlUp).Offset(0, 2).Value = Sheets("Cal_Cert").Range("B20").Value
Sheets("Cert_List").Range("A65536").End(xlUp).Offset(0, 3).Value = Sheets("Cal_Cert").Range("B22").Value
Sheets("Cert_List").Range("A65536").End(xlUp).Offset(0, 4).Value = Sheets("Cal_Cert").Range("B24").Value
Sheets("Cert_List").Range("A65536").End(xlUp).Offset(0, 5).Value = Sheets("Cal_Cert").Range("G13").Value
Sheets("Cert_List").Range("A65536").End(xlUp).Offset(0, 6).Value = Sheets("Cal_Cert").Range("G15").Value
Sheets("Cert_List").Range("A65536").End(xlUp).Offset(0, 7).Value = Sheets("Cal_Cert").Range("G17").Value


'To save file as incremented Certificate Number
ThisWorkbook.SaveAs "C:\Documents and Settings\Steve\My Documents\Work\Certificates\" & Sheets("Cal_Cert").Range("G11").Value & "_" _
& Sheets("Cal_Cert").Range("J11").Value & ".xls"
End Sub