Adding Rows Based on Cell Value

Hello Everybody,

I am trying to find out how to add a row beneath a specified cell automatically, based on some other cell's value. For example, my data is in column A and B. If C2 is assigned a value of "Y:, I would like an empty row inserted beneath my second row, shifting the value of A3 and B3 down one row to A4 and B4 respectively. Thus far I've managed to develop this macro:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errHnd
If Target.Column = 3 Then
If Target = "Y" Then
Application.EnableEvents = False
Range("A" & Target.Row + 1).Insert Shift:=xlDown
Range("B" & Target.Row + 1).Insert Shift:=xlDown
Range("C" & Target.Row).Insert Shift:=xlDown
Application.CutCopyMode = False
End If
End If
errHnd:
'Re-enable event
Application.EnableEvents = True
End Sub


Which works fine, except that I need the process to be automated. Currently, this macro will only activate if I manually enter the value "Y" into a C cell and press enter; if the cell in C acquires the value "Y" by any other method the macro will not activate. This makes it impossible for me to use this macro to evaluate large amounts of data, since I can't auto-populate C with predefined "Y" value cells.

I know the issue probably lies in the ever changing nature of the columns, but I included a line to shift the C value down to keep all three columns in sync with one another with respect to their original data, hopefully eliminating any circular processes (though I don't know if this will prove necessary in the end; it was just my guess). Any advice would be greatly appreciated!

The following macro needs to be triggered manually but it will look at all cells in column C and add a row below the cells containing the letter "Y".

Please Login or Register  to view this content.