Insert New Row
 

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

Insert New Row
 

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

Insert New Row
 

Sub InsertAfterFind()
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(1).Value = newtext
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"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

Insert New Row
 

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

Insert New Row
 

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

Insert New Row
 

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

Insert New Row
 

I thought I did, my mistake for not making it clear. Thanks very much

--

Regards
Michael Koerner


"Don Guillett" wrote in message
...

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

Insert New Row
 

I looked at the VBA example. But have no idea how to incorporate that into
what you wrote originally.

--

Regards
Michael Koerner


"Don Guillett" wrote in message
...

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

Insert New Row
 

Use this

Sub InsertAfterFindALL()
What = "3-SuperStore"
newtext = "4-Independent"

With ActiveSheet.Columns("e")
Set c = .Find(what, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Offset(1).EntireRow.Insert
c.Offset(, -4).Resize(, 4).Copy c.Offset(1, -4)
c.Offset(1).Value = newtext
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If

End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Michael Koerner" wrote in message
...
I looked at the VBA example. But have no idea how to incorporate that into
what you wrote originally.

--

Regards
Michael Koerner


"Don Guillett" wrote in message
...

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

Insert New Row
 

Don;

Thanks you very much. Worked like a charm

--

Regards
Michael Koerner


"Don Guillett" wrote in message
...
Use this

Sub InsertAfterFindALL()
What = "3-SuperStore"
newtext = "4-Independent"

With ActiveSheet.Columns("e")
Set c = .Find(what, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Offset(1).EntireRow.Insert
c.Offset(, -4).Resize(, 4).Copy c.Offset(1, -4)
c.Offset(1).Value = newtext
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If

End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Michael Koerner" wrote in message
...
I looked at the VBA example. But have no idea how to incorporate that into
what you wrote originally.

--

Regards
Michael Koerner


"Don Guillett" wrote in message
...

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