New row with macro --> pasting a 'template'

[StephanV]

My worksheet contains 27 columns (A to AA) and has multiple content, 'normal data' like names/certificates, dates and checkboxes. With this sheet i can keep myself informed about when a certificated has to be renewed. First i will give some background information.

I do this like this:

A2:A20 = startdate
B2:B20 = Current date generated with =+NOW()
C2:C20 = Signaldate generated with a VBA macro see below at the *1 whichs adds 150 days to the startdate
D2:D20 = End-date generated with a VBA macro see below at the *2, whichs adds 183 days to the startdate
E2:E20 = Give signal, because this has to be between B2 and C2, it will display "CV1" when neccecary =IF(AND(D2>=B2;D2<=C2);"CV1";"")
F2:F20 = Expiration date, and date cannot be higher than the current date, if so:  =IF(D2>C2;"Certificate expired!";"")
G2:G20 = Here comes the first VBA-checkbox which adds the amound of days neccecary to go in the next fase. 

=== START FASE 2 ===

Like above, so that the days added to the start days increase with 300 and 365 days. 

==== This goes on to a fourth and last fase, so a loop of 2 years is completed, after this a VBA-button is pressed which unchecks all checkboxes, so that the loop goes on ====

*1

Private Sub Chk_A1_Change()
        If chk_A1.Value = True Then
            Date = Worksheets("Sheet1").Range("B1").Value
            Worksheets("Sheet1").Range("B1").Value = Date + 183
                 Else
                        Exit Sub
                    End If
            End Sub

*2

Private Sub ChkA2_Change()
   If chk_A1.Value = False Then
      Exit Sub
   Else
        If chk_A2.Value = True Then
            Date = Worksheets("Sheet1").Range("B1").Value
            Worksheets("Sheet1").Range("B1").Value = Date + 183
                 Else
                        Exit Sub
                    End If
            End If
        
End Sub

Checkbox 3 and 4 are the same as above, but reffer to a different column offcourse.

The uncheckallbutton is like this:

Private Sub CommandButton1_click()
If CommandButton1.Enabled = True Then
        chk_A1.Value = False
        CheckBox2.Value = False
        CheckBox3.Value = False
        CheckBox4.Value = False
    Else
        If CommandButton1.Enabled = False Then
            Exit Sub
        End If
    End If
End Sub

So far so good (in my opinion) but now the hard part, i cant get it figured out..

Is it possible to create a button or macro which creates a new row with all the checkboxes from the rows above, along with all the 'template' used in all the rows above? The checkboxes should apply to this new row only.

This will help me in two ways:
1) I expect to have 150 rows. When i have the option like described above i dont have to make 150 x 4 checkboxes and macro's by hand.
2) The sheet will become idiot-proof, so that when a new name with new data (and checkboxes) can be added by everyone, simply by using a dropdown menu and adding new dates.

I hope the store makes a little bit sense and mostly i hope someone can help me!

 

[StephanV]

Ok, new update:
My workbook contains 2 sheets.

Sheet 1 is a form in which the user puts the name/idnumber/materials/startdate when pressed "ok" the data will be cut/pasta in the last row of sheet 2, the database.

It doesn't solve my problem described above. Is it possible to write a macro that 'makes' checkboxes with row dependent variables?