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

 

[DanteAmor]

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$H$6" Then
    If Target.Count > 1 Then Exit Sub
    
    On Error Resume Next
    Range("GreenQ1").EntireRow.Delete
    ActiveWorkbook.Names("GreenQ1").Delete
    On Error GoTo 0
    If Target.Value > 0 Then
      Range("GreenTemplate").Copy
      Range("insertRow").Offset(1).Insert Shift:=xlDown
      Application.CutCopyMode = False
      ActiveWorkbook.Names.Add Name:="GreenQ1", RefersToR1C1:="=report!R7:R9"
    End If
  End If
End Sub

 

[jtatt]

Thank you Dante

This resulted in run-time error 1004
method 'range' of object_worksheet failed

and this row of code highlighted yellow
Range("GreenTemplate").Copy

 

[DanteAmor]

Hello
I am trying to write a change event which when the value in H6 is >0 the named range "GreenTemplate"

According to your requirement, you must have a named range such as "GreenTemplate"

 

[jtatt]

thank you
yes there is a named range "GreenTemplate"

 

[DanteAmor]

thank you
yes there is a named range "GreenTemplate"

Do you have it on the same sheet or is it on another sheet?
Check the name. Works for me.

 

[jtatt]

it is on a different sheet

 

[DanteAmor]

it is on a different sheet

Change this

Range("GreenTemplate").Copy

by:

Sheets("[COLOR=#ff0000]SheetX[/COLOR]").Range("GreenTemplate").Copy

Change "SheetX" by the name of your sheet

 

[jtatt]

thank you Dante

that stopped that bug. I had to add sheets report also.
now the code looks like below. I ran it. With the insert row at row 7, it is copying from the template three rows which are now rows 8:10 which I need to be the named range, but the named range has named rows 7 to 9
Is there a way to have it name the new inserted rows without reference to rows 7 to 9 as the insert row may have moved?



Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$H$6" Then
If Target.Count > 1 Then Exit Sub

On Error Resume Next
Range("GreenQ1").EntireRow.Delete
ActiveWorkbook.Names("GreenQ1").Delete
On Error GoTo 0
If Target.Value > 0 Then
Sheets("templates").Range("GreenTemplate").Copy
Sheets("report").Range("insertRow").Offset(1).Insert Shift:=xlDown
Application.CutCopyMode = False
Sheets("report").Names.Add Name:="GreenQ1", RefersToR1C1:="=report!R7:R9"
End If
End If
End Sub

 

[DanteAmor]

But the reference is "insertRow", so it's always going to be 7:9
When does it change to 8?

Forget the code for a moment.
You can explain a sequence of activities, starting from the first execution, execution2, execution3, delete, execution4, delete, etc.
Try to explain each scenario.
And showing how the sheet should remain in each execution or deletion.