Greetings, I'm new to this forum but have visited many times for great ideas. This time i'm stumped.

I have an applications tracking chart with dozens of rows. I'd like to make key cells in each row corresponding to a new applicaiton mandatory if data is input to any cell in the row.

I also have a macro that copies the formats and formulas from the last row of the chart to the next row down which works reasonably well. I provide it here for context:

Sub CopyMacro()
ActiveSheet.Range(Range("AD65536").End(xlUp), "AD" & Range("AD65536").End(xlUp).Row).Copy
ActiveSheet.Range("AD65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteFormulas
ActiveSheet.Range(Range("X65536").End(xlUp), "X" & Range("X65536").End(xlUp).Row).Copy
ActiveSheet.Range("X65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteFormulas
ActiveSheet.Range(Range("R65536").End(xlUp), "R" & Range("R65536").End(xlUp).Row).Copy
ActiveSheet.Range("R65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteFormulas
ActiveSheet.Range(Range("O65536").End(xlUp), "O" & Range("O65536").End(xlUp).Row).Copy
ActiveSheet.Range("O65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteFormulas
ActiveSheet.Range("a65536").End(xlUp).EntireRow.Copy
ActiveSheet.Range("a65536").End(xlUp).Offset(1, 0).EntireRow.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
End Sub

To make cells in the newly created row manadatory i did the following:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = False
Application.EnableEvents = True

If Trim(Range("A8")) <> "" Then
If Trim(Range("B8")) = "" Then Cancel = True
If Trim(Range("C8")) = "" Then Cancel = True

If Trim(Range("E8")) = "" Then Cancel = True
If Trim(Range("F8")) = "" Then Cancel = True
If Trim(Range("G8")) = "" Then Cancel = True
If Trim(Range("H8")) = "" Then Cancel = True
If Trim(Range("I8")) = "" Then Cancel = True
If Trim(Range("J8")) = "" Then Cancel = True
If Trim(Range("K8")) = "" Then Cancel = True
If Trim(Range("L8")) = "" Then Cancel = True
If Trim(Range("M8")) = "" Then Cancel = True
If Trim(Range("N8")) = "" Then Cancel = True
End If

If Trim(Range("A9")) <> "" Then
If Trim(Range("F9")) = "" Then Cancel = True
If Trim(Range("G9")) = "" Then Cancel = True
If Trim(Range("H9")) = "" Then Cancel = True
End If

If Cancel = True Then MsgBox "Please fill in all the cells required."

End Sub

D8 is left out because i want it to be mandatory only if C8="registered"

The problem I'm having is the cells are only mandatory if data is input in A8, How do I specify a range like A8:N8, or A8, B8, C8.... in the initial argument? I tried doing this and it did nothing.

Also I want this to work for every new row created and so far have only thought of repeating the code for A9, A10 etc., but this will quickly become overwhelming, is there a way of simplifing this?

Any help that may be offered is greatly appreciated.