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]

Scenarios


three sheets "control" "report" "templates"



"templates" sheet contains a number of named rows which will be copied
into the "reports" sheet as inserted copied cells upon a change event in "control" sheet cells

one of the named ranges in templates is "GreenTemplate"


"control sheet" contains a number of "target" cells which
when a number greater than zero is keyed in
create event to copy named rows from "templates" sheet into "report"


when named range "GreenTemplate" from "templates" is copied into "report"sheet
it is required to be inserted rows at
a named row "insertRow" so that the result is the copied rows sit above the insertRow

"GreenTemplate" is three rows deeps, the different templates have different
numbers of rows

prior to any events "report" sheet named range "insertRow" is row 7

after copying "GreenTemplate" into "report" at insert row, insert row is now row10



The three rows of "GreenTemplate" copied into "report" are then named "GreenQ1"
and are now in rows 7:9

other unrelated events at other insertion rows may move row 7 down the "report"
sheet before the "GreenTemplate" copy event so "GreenQ1" might end up further
down "report" sheet than rows 7:9






The named rows copied in from "templates" vary in # of row depth also


if the user then clears the number "control" H6 it deletes the
inserted rows from "templates"and deletes the name which was created "GreenQ1"

I will try to insert images

 

[DanteAmor]

Now I am more confused. I understood all that theory, copy and paste. Now explains, first time the process is done. which rows are copied and where they paste, tell me the row numbers, because I don't see your sheet and I don't know where your named ranges are. Second execution, what must happen. or what is your idea?

 

[jtatt]

my apologies for confusion

first time on opening workbook before process

in templates sheet "GreenTemplate" is rows 7:9

in report sheet, "insertRow" is row 7

[also before any process in report sheet on row 10 is named range "insertBlueRow" which is part of separate event that copies and inserts copied cells- a different named range- from "templates"]

user activates event in "control" cell H6 by keying in number > 0

now "templates" rows named range "GreenTemplate" on rows 7:9 copied and insert as copied cells function in "report" sheet at named range "insertRow" which is row 7

now "report" sheet rows 7:9 contain contents of "GreenTemplate" and "insertRow" is now
row 10

rows 7:9 of "report" sheet need to be named "GreenQ1" but as there may have a different event which has moved all these copies down the sheet the code needs to name the copied cells whichever rows they are in, it might not be 7:9


Due to the event above occuring "insertBlueRow" has moved down from row 10 to row 13 so the event code which i envisage will work the same way as "Green" needs to be flexible enough to allow for changes created by inserted rows in "report" sheet

I will try to insert HTML below
this is the control sheet before any scenarios cells G6:H7

Book1
	G	H
6	insert Green Template
7	insert Blue Template
control

Cell Formulas
Range	Formula
G6	insert Green Template
G7	insert Blue Template

hopefully - this is the report sheet before any scenarios A7:E10

Book1
	A	B	C	D	E
7	--------------------------------------------------------------
8
9
10	-----------------------------------------------------
report

Cell Formulas
Range	Formula
A7	--------------------------------------------------------------
A10	-----------------------------------------------------

this is "templates" sheet A7:E14 prior to any events

Book1
	A	B	C	D	E
7
8
9
10
11
12
13
14
templates

 

[DanteAmor]

Try the following and tell me what it would be missing.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$H$6" Then
    If Target.Count > 1 Then Exit Sub
    If Target.Value > 0 Then
      Sheets("SheetX").Range("GreenTemplate").Copy
      Range("insertRow").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
It works if the workbook is opened and scenario run without doing anything else to the workbook.

If another scenario is run on sheet "report" e.g. inserting 4 rows on report tab above "insert row" then rows 7:9 are named "GreenQ1" when the rows copied from "templates" are on rows 11:13

as below

￿￿

￿￿

 

[DanteAmor]

Thank you Dante
It works if the workbook is opened and scenario run without doing anything else to the workbook.

If another scenario is run on sheet "report" e.g. inserting 4 rows on report tab above "insert row" then rows 7:9 are named "GreenQ1" when the rows copied from "templates" are on rows 11:13

as below

￿￿

￿￿

The images are not visible.
Now, I don't understand again. Why do you say 4 rows. If Range ("GreenTemplate") only has 3 lines.

 

[jtatt]

thank you for your assistance Dante. I meant three rows. I apologize if I am not explaining very well. I could not get the screen shots to copy last time.
There are a number of insert rows which will move down the report sheet depending on which templates the users copy in
I think what I am looking for is a way for the template items to be copied and inserted into report sheet in a relative way. If the named row "insert" happens to be at row 7

Book1
	A	B
4	-------------------------------------------------------------
5
6
7	--------------------------------------------------------------
8
9
10	-----------------------------------------------------
report

Cell Formulas
Range	Formula
A4	-------------------------------------------------------------
A7	--------------------------------------------------------------
A10	-----------------------------------------------------

then the copied rows from templates will be inserted at row 7 with shift cells down and the three rows of named cells will be in rows 7 to 9 as below

Book1
	A	B
4	-------------------------------------------------------------
5
6
7
8
9
10	--------------------------------------------------------------
11
12
13	-----------------------------------------------------
report

Cell Formulas
Range	Formula
A4	-------------------------------------------------------------
A10	--------------------------------------------------------------
A13	-----------------------------------------------------

in this instance rows 7:9 need to be named "GreenQ1"

however if prior to inserting green, someone had copied rows above the yellow row as below

Book1
	A	B	C	D	E
4
5
6
7	-------------------------------------------------------------
8
9
10	--------------------------------------------------------------
11
12
13	-----------------------------------------------------
report

Cell Formulas
Range	Formula
A7	-------------------------------------------------------------
A10	--------------------------------------------------------------
A13	-----------------------------------------------------

then the green insert row would sit further down the report sheet , in this instance "insertRow" is now on row 10
so if the code is actived and GreenTemplate copied into report sheet, the resultant "GreenQ1" would be at rows 10:12 so rows 10:12 need to be named not rows 7:9

Book1
	A	B	C	D
4
5
6
7	-------------------------------------------------------------
8
9
10
11
12
13	--------------------------------------------------------------
14
15
16	-----------------------------------------------------
report

Cell Formulas
Range	Formula
A7	-------------------------------------------------------------
A13	--------------------------------------------------------------
A16	-----------------------------------------------------

thank you for your constant help so far

 

[DanteAmor]

Now please, try this

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$H$6" Then
    If Target.Count > 1 Then Exit Sub
    If Target.Value > 0 Then
      Dim r As Long
[COLOR=#0000ff]      r = Range("insertRow").Row[/COLOR]
      Sheets("SheetX").Range("GreenTemplate").Copy
      Range("insertRow").Insert Shift:=xlDown
      Application.CutCopyMode = False
      ActiveWorkbook.Names.Add Name:="GreenQ1", RefersToR1C1:=[COLOR=#0000ff]"=report!R" & r & ":R" & r + 2[/COLOR]
    End If
  End If
End Sub

 

[jtatt]

It has run-time error 1004
Method 'Range' of object'_Worksheet' failed

the yellow row is r = Range("insertRow").Row

 

[DanteAmor]

It has run-time error 1004
Method 'Range' of object'_Worksheet' failed

the yellow row is r = Range("insertRow").Row

I'm lost.


Do you have a named range "insertRow" and that named range is on the "Report" sheet?


The change event are you putting on the "Report" sheet?


Try t

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$H$6" Then
    If Target.Count > 1 Then Exit Sub
    If Target.Value > 0 Then
      Dim r As Long
      r = [COLOR=#ff0000]Sheets("Report")[/COLOR].Range("insertRow").Row
      Sheets("SheetX").Range("GreenTemplate").Copy
      [COLOR=#ff0000]Sheets("Report")[/COLOR].Range("insertRow").Insert Shift:=xlDown
      Application.CutCopyMode = False
      ActiveWorkbook.Names.Add Name:="GreenQ1", RefersToR1C1:="=report!R" & r & ":R" & r + 2
    End If
  End If
End Sub

If you have another named range problem, it is because I am not understanding on which sheet the macro is, on which sheet the named range is.
Then you could upload a file to the cloud.

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.