Solved: please rectify auto row insert

[vipulhumein]

hi
in my sheet my target column(i.e column L) had a formula which is hidden so that no one can access or edit the formula and the formula is H-O . I WANT THAT IF IN MY TARGET COLUMN ANY VALUE MORE THAN 0 THAN AUTO INSERT ROW
please help me by rectifying the below vba

thanks in advance


Private Sub Worksheet_Change(ByVal Target As Range)
Const DISP_PWD = "123"
Dim i As Long

If Target.Column = 12 And Target.Row > 5 Then
If Target > 0 Then
If MsgBox("Add new row?", vbYes, "Change Sheet") Then
Updating = True
ActiveSheet.Unprotect Password:=DISP_PWD
Cells(ActiveCell.Row + 1, ActiveCell.Column).Select
Rows(ActiveCell.Row & ":" & ActiveCell.Row).Insert
Cells(ActiveCell.Row, ActiveCell.Column) = Cells(ActiveCell.Row + 1, ActiveCell.Column)
Cells(ActiveCell.Row + 1, ActiveCell.Column) = ""
For i = 1 To 10
Cells(ActiveCell.Row, i).Formula = Cells(ActiveCell.Row - 1, i).Formula
Next i
CopyCells 11
CopyCells 12
CopyCells 17
CopyCells 19
CopyCells 24
CopyCells 25
CopyCells 26
CopyCells 27
CopyCells 28
CopyCells 30
Cells(ActiveCell.Row, 20).Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:=DISP_PWD
Updating = False
End If
End If
End If
end sub

[xls]

what error you are getting? elaborate little more

[p45cal]

The change event doesn't trigger on a formula's result changing. In your case, column 12 is column L, which is hidden and has a formula dependent on columns O and H.
Do the cells in O and H also contain formulae?
Are these the cells which the user changes?
If not which cell(s) does the user change which ultimately causes a change in the value of column L?

[vipulhumein]

column h is linked with other sheet, so cell of column O causes a change in column L( i.e cloumn O which the user changes)

[p45cal]

So we need to be starting with the likes of:If Target.Column = 15 And Target.Row > 5 Then
If Cells(Target.Row, "L").Value > 0 Then
Now you have lots of ActiveCells in your code. Very confusing. The target cell/active cell will be in column O now, not column L. Also the active cell is usually the cell below the one that's just changed (unless you've changed a setting in Excel to move the active cell across rather than down on pressing Enter, or you've held the Ctrl key down while you've pressed Enter).
Can you say, in words rather than code, what you want to happen relative to the target.row?