Event Macro

[Amy Beryl]

Hi All -

I am a new macro writer and my goal is to produce a macro that, at the cursor position, inserts 2 rows and copies a range into it. Then I want the macro to wait until I move to the next insertion point and then do the same thing again. And repeat until I've run out of departments - which is something I will determine. So far I have:

Private Sub Worksheet_Change(ByVal Target As Range)

ActiveCell.Resize(2,1).EntireRow.Insert
Range("A5:I6").Copy Destination:=ActiveCell

End Sub

I'm stumped. Any help (or pity) would be greatly appreciated.

Amy

 

[MrKowz]

A few questions:

1. What determines an "insertion point"?
2. When, exactly, do you want this macro to run?
3. Will it always be A5:I6 to copy?

 

[Amy Beryl]

I imagine that, after a successful running of the macro, I would scroll down to the next department and do something - enter, edit - anything that would trip the macro to run again. The trigger doesn't really matter. I just want to move to the next place I want to copy the data and metaphorically yell "GO" and have it execute. Then the macro should wait until I find the next department.

 

[stnkynts]

Is the copied range always going to be A5:I6 for each time you click? If so then try this:

Note: I put it on BeforeDouble-Click. Could lead to less problems this way.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
 
Target.Offset(1, 0).Insert Shift:=xlDown
Target.Offset(1, 0).Insert Shift:=xlDown
Range("A5:I6").Copy Destination:=Target.Offset(1, 0)
 
End Sub

 

[MrKowz]

Try the following SelectionChange event macro. This will fire whenever you change the cell you are currently on; so be careful what you click!

Private Sub Worksheet_SelectionChange(ByVal target As Range)
Application.EnableEvents = False
target.Resize(2, 1).EntireRow.Insert
Range("A5:I6").Copy Destination:=target
Application.EnableEvents = True
End Sub