vba change event with cases copying a range and inserting at a named row stipulates rows used which may change

[jtatt]

Hello
I am trying to write a change event which when the value in H6 is >0 the named range "GreenTemplate"(which is three rows deep) is copied and inserted below named range "insertRow" and then the inserted rows are named "GreenQ1".
Then if the value in H6 is cleared the named range is deleted as are the inserted rows.

This only works if no rows are inserted above "insertRow" prior to event

Rows are likely to be inserted above "insertRow" before the event is triggered then the event does not work for inserting or deleting

Is there some clever member who can see a way to resolve this please
Thank you




Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$H$6" Then
Select Case Target.Value
Case Is > 0
Application.Goto Reference:="GreenTemplate"
Selection.Copy
Application.Goto Reference:="insertRow"
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False
ActiveWorkbook.Names.Add Name:="GreenQ1", RefersToR1C1:="=report!R7:R9"
ActiveWorkbook.Names("GreenQ1").Comment = ""
Sheets("control").Select
Range("B3").Select
Case Is = ""
Application.Goto Reference:="GreenQ1"
Selection.Delete Shift:=xlUp
ActiveWorkbook.Names("GreenQ1").Delete
Sheets("control").Select
Range("B3").Select
End Select
End If
End Sub

 

[jtatt]

that worked perfectly, thank you so much Dante

 

[DanteAmor]

that worked perfectly, thank you so much Dante

I'm glad to help you. Thanks for the feedback.