Dear Experts,
by using the below code:
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
it allows to insert a new row by copying above cell with existing data on above row/cell and then clean specific range of cells / add specific formula to a cell and insert below.
It works well when the filters are removed/not actived, but when the filter of any range is active, the VBA code only inserts completely blank row and no data is copied from above row nor below formula is inserted:
Cells(r, "Q").Formula = _
"=IF(OR(P" & r & "="""",P" & r & "=""Insert Date""),"""",IF(P" & r & ">M" & r & ",""Define reason for delay"",""""))"
How can this coding be modified when the filters are active to work as it is working even if the filters are not activated?
Thanks!
N
Bookmarks