You should have mentioned that in the ORIGINAL post
Use FINDNEXT . See example in vba help index
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Michael Koerner" wrote in message
...
Don;

Thanks very much. How do I make it go through the whole sheet 1400 rows
and
make the change each time it hits 3-SuperStore?

--

Regards
Michael Koerner


"Don Guillett" wrote in message
...
Sub InsertAfterFindCopyRowAbove()
What = "3-SuperStore"
newtext = "4-Independent"
With Columns("e").Find(What, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
.Offset(1).EntireRow.Insert
.Offset(, -4).Resize(, 4).Copy .Offset(1, -4)
.Offset(1).Value = newtext
End With
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Michael Koerner" wrote in message
...
Forgot to add that I would also like whatever is in Columns A, B, C, and
D
copied from the row above the new one copied into the new row. Hope this
makes sense

--

Regards
Michael Koerner


"Michael Koerner" wrote in message
...
I would like to be able to insert a new row labelled 4-Independent after
the
row that is labelled 3-SuperStore in Column "E"

--

Regards
Michael Koerner