Automatically adding lines and copying certain cells from one sheet to another

[petepike]

Hi,
I have a VERY basic knowledge and understanding of VB so please be gentle.... lol

I am attemping to make a spreadsheet that holds information such as Delegates Name, Company, ID number, Course Type, Dates, etc.
and by clicking a button at the end of each row labelled 'Certificate' it will check for empty cells first and highlight any missing fields, then copy the required information into a Certificate layout in a different sheet (currently Sheet 3)
I can easily make it do it for one row using the following simple code and macro:-

Sub To_Certificate()
Sheets("Sheet1").Select
If Range("B5") = "" Then
MsgBox "Delegates 'Name' is required", vbCritical, "Missing Data"
Exit Sub
ElseIf Range("C5") = "" Then
MsgBox "Delegates 'Company Name' is required", vbCritical
Exit Sub
ElseIf Range("D5") = "" Then
MsgBox "Delegates 'Security I.D. Number' is required", vbCritical
Exit Sub
ElseIf Range("E5") = "" Then
MsgBox "Delegates 'Medical Certificate Date' is required", vbCritical
Exit Sub
ElseIf Range("F5") = "" Then
MsgBox "'Course Type' field is blank", vbCritical
Exit Sub
ElseIf Range("G5") = "" Then
MsgBox "'Initial/Renewal field is blank", vbCritical
Exit Sub
ElseIf Range("H5") = "" Then
MsgBox "'Course Date' is required", vbCritical
Exit Sub
ElseIf Range("H5") = "" Then
MsgBox "'Instructors Name' is required", vbCritical
Exit Sub
End If

'
' To_Certificate Macro
'
'
Sheets("Sheet3").Select
Range("A13:R13").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R5C2"
Range("A17:R17").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R5C3"
Range("A21:R21").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R5C4"
Range("A25:R25").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R5C5"
Range("F37:K37").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R5C9"
Range("F38").Select

End Sub


However.... I have 'borrowed' another bit of code which adds more lines to the database and I was hoping I could incorporate into this code the ability to automatically update the code in the button which you press to generate a certificate..... I hope that makes sense?!?!

Here's the code I use which adds more lines:-

Sub InsertRowsAndFillFormulas()
'adds desired # of lines below the current line and
' copies the formulas to that/those lines
'added selection of more than one worksheet
' - Gary L. Brown
' - Kinneson Corp. 01/17/2001
' - modification from thread discussion in
' Microsoft.Public.Excel.Programming newsgroup
' on 01/17/2001
' Re: Insert Rows -- 1997/09/24 Mark Hill
' The original macro is described in
' http://www.geocities.com/davemcritchie/excel/insrtrow.htm
Dim vRows As Long
Dim sht As Worksheet, shts() As String, i As Long
' row selection based on active cell --
' rev. 2000-09-02 David McRitchie
ActiveCell.EntireRow.Select
vRows = _
Application.InputBox(prompt:= _
"How many rows do you want to add?", Title:="Add Rows", _
Default:=1, Type:=1) 'type 1 is number
If vRows = False Then Exit Sub
'if you just want to add cells and not entire rows
' then delete ".EntireRow" in the following line
ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _
Windows(1).SelectedSheets.Count)
i = 0
'insert rows on grouped worksheets
' rev. 2001-01-17 Gary Brown
For Each sht In _
Application.ActiveWorkbook.Windows(1).SelectedSheets
Sheets(sht.Name).Select
i = i + 1
shts(i) = sht.Name
Selection.Resize(rowsize:=2).Rows(2).EntireRow. _
Resize(rowsize:=vRows).Insert Shift:=xlDown
Selection.AutoFill Selection.Resize(rowsize:=vRows + 1), _
xlFillDefault
On Error Resume Next
' to remove the non-formulas -- 1998/03/11 Bill Manville
Selection.Offset(1).Resize(vRows).EntireRow. _
SpecialCells(xlConstants).ClearContents
Next sht
'reselect original group - Dave McRitchie 01/17/2001
Worksheets(shts).Select
End Sub


Basically I want to be able to press a button at the end of the row which is labelled 'Certificate' and it will copy information from certain cells to another sheet. When I add new lines to the table I want to be able to press a button which says 'Add more lines' and it will copy all the layout, formatting, codes, buttons, etc. onto the next line down so that when I put information into this row and hit 'Certificate' it goes to Sheet 3 and inputs all the information in the correct places.

Sorry, I know I probably havent explained this very well. I hope someone can make sense of my ramblings!

Many thanks in advance

Peter