Where do you insert that row in sheet1? I guessed at the top.

Option Explicit
Sub testme01()

Dim FoundCell As Range
Dim FirstAddress As String
Dim fromWks As Worksheet
Dim toWks As Worksheet
Dim findWhat As String
Dim destCell As Range

findWhat = "newyork"

Set fromWks = Worksheets("sheet2")
Set toWks = Worksheets("sheet1")

With fromWks
Set FoundCell = .Cells.Find(what:=findWhat, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, lookat:=xlWhole, _
searchorder:=xlByRows, searchdirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
MsgBox "not found on " & fromWks.Name
Exit Sub
End If

FirstAddress = FoundCell.Address

Do
With toWks
.Rows(1).Insert
Set destCell = .Range("A1")
End With

FoundCell.EntireRow.Copy _
Destination:=destCell

Set FoundCell = .Cells.FindNext(FoundCell)

Loop While Not FoundCell Is Nothing _
And FoundCell.Address < FirstAddress
End With

End Sub

I also guessed that newyork was the only value in the cell (xlWhole).

Kyle80 wrote:

Please help! I'm a fairly experienced programmer, who is new to Excel.

Here's an example of what I need to do:

1- Find a Row in "Sheet2" with "NEWYORK" in it
2- If found, Automatically Insert (and shfit cells down) a ROW in
"Sheet1"
3- Copy values of row from "Sheet2" into the new Row created in Sheet1
4- Repeat until no more occurrences of "NEWYORK" are found

Any help would be greatly appreciated!

Cheers,
Kyle

--

Dave Peterson