Disallow macro to function within range of rows

[nerzzul]

Hello Experts,

by using below code, i am able to duplicate a selected row and copy it below by removing some of the data in defined areas,

Sub insert_new_item ()

ActiveCell.EntireRow.Copy
ActiveCell.Offset(1, 0).EntireRow.Insert
Application.CutCopyMode = False

Dim r As Long: r = ActiveCell.Offset(1, 0).Row
Cells(r, "I").Resize(, 6).Value = ""
Cells(r, "Q").Formula = _
    "=IF(OR(P" & r & "="""",P" & r & "=""Insert Date""),"""",IF(P" & r & ">M" & r & ",""Define reason for delay"",""""))"

End Sub

Is it possible to revise the code and do not allow to copy/work/function within range of 1 to 5? as my headers and macro buttons are located within this range.

lets say, user selects row 4 and when click's on the macro button, error message would popup by telling the user to select table range etc... and macro would not do anything.

appreciate your valued support.

Thanks,
N

[ByteMarks]

How about.

Sub insert_new_item()

If ActiveCell.Row <= 5 Then MsgBox "Please select a cell on a valid row.", vbCritical: Exit Sub

ActiveCell.EntireRow.Copy
ActiveCell.Offset(1, 0).EntireRow.Insert
Application.CutCopyMode = False

Dim r As Long: r = ActiveCell.Offset(1, 0).Row
Cells(r, "I").Resize(, 6).Value = ""
Cells(r, "Q").Formula = _
    "=IF(OR(P" & r & "="""",P" & r & "=""Insert Date""),"""",IF(P" & r & ">M" & r & ",""Define reason for delay"",""""))"

End Sub

[nerzzul]

Hi ByteMarks, that was a nice solution! worked like a charm.

Would also be possible to let it do the same within specific rows of the worksheet?

such as, my other subheaders are located in below rows, their location on row is not fixed, due to this macro adding new items for tasks and headers are shifting down and down.

So, the macro would stop again when it finds a header which is located in Column B, header=Management, these various subheaders with different text do also appear on below rows.

Thanks,
N

[ByteMarks]

Thanks for the feedback.

I guess we'd need some way to test if column B was a header.

Maybe if we can assume that there'll be no formula in column Q if it's a header row?

Sub insert_new_item()

If ActiveCell.Row <= 5 Or Range("Q" & ActiveCell.Row).HasFormula Then MsgBox "Please select a cell on a valid row.", vbCritical: Exit Sub

ActiveCell.EntireRow.Copy
ActiveCell.Offset(1, 0).EntireRow.Insert
Application.CutCopyMode = False

Dim r As Long: r = ActiveCell.Offset(1, 0).Row
Cells(r, "I").Resize(, 6).Value = ""
Cells(r, "Q").Formula = _
    "=IF(OR(P" & r & "="""",P" & r & "=""Insert Date""),"""",IF(P" & r & ">M" & r & ",""Define reason for delay"",""""))"

End Sub

[nerzzul]

Hi ByteMarks,

my apologies for the late reply, was traveling and just got back.

the solution provided has worked, as i just created a dummy hidden formula at the other column and all went well after the test.

Thanks so much!