VBA code to find specific text in multiple rows, copy two rows above the row with text, and then paste to the row above that

[Samizell]

Hi Everyone,

I'm a newbie to VBA, I typically create a Macro and edit it from there, but am having troubles with this one. I'm trying to create a button that will allow the user to 'insert a row' in 3 different sections. At the end of each section I have hidden text that says "Insert Row". An example of what I'm looking for...

I need the macro to look at column B for text "Insert Row" (there are currently 3 cells that contain "Insert Row"; B42, B67, and B92). I then want to copy rows 40, 65, and 90 (so the second row above the row containing "Insert Row") and paste it to the row above that...so pasting to 39, 64, and 89. But the user needs to be able to insert as many rows as they need, so row numbers can't be absolute. Is this possible?

TYIA!
Shelly

 

[Samizell]

This is what I currently have

Sub Insert_Rows()


Dim insert As Range
Set insert = Columns("B").EntireRow.Find(what:="Insert Row", LookIn:=xlValues, Lookat:=xlWhole)
If Not insert Is Nothing Then
insert.Select
Do
Rows(Selection.Row - 2).Copy
Rows(selectoin.Row - 2).insert Shift:=x1up
Set insert = Columns("B").EntireRow.FindNext(insert)
Loop While Not insert Is Nothing
End If
End Sub