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

Thank you for your help!

What if I wanted to insert a row in Sheet1 AFTER the first occurrance
of NewYork?


Sheet1:

Total Breakdown by City

NEW YORK
NEWYORK 12 40 93 83 00 29 48
NEWYORK 20 89 85 01 92 30 93

BOSTON
BOSTON 20 102 94 29 10 48 29
BOSTON 50 152 54 39 15 43 59

etc.

Thanks!

This seems to work ok:

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 toWks
Set destCell = .Cells.Find(what:=findWhat, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, lookat:=xlWhole, _
searchorder:=xlByRows, searchdirection:=xlNext, _
MatchCase:=False)
If destCell Is Nothing Then
MsgBox findWhat & " wasn't found on " & toWks.Name
Exit Sub
End If
End With

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
destCell.Offset(1, 0).EntireRow.Insert
FoundCell.EntireRow.Copy _
Destination:=destCell.Offset(1, 0)
Set FoundCell = .Cells.FindNext(FoundCell)

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

End Sub

But a suggestion. Don't use that extra line in your data. It make look very
pretty, but it may screw up things later. If you ever want to apply subtotals
to get a count per city, you'll have to do things differently. It may make
pivottables and charts more difficult.

I'd just apply Data|filter|autofilter to my range and then I could show the city
and all its data by using that filter.

Kyle80 wrote:

Thank you for your help!

What if I wanted to insert a row in Sheet1 AFTER the first occurrance
of NewYork?

Sheet1:

Total Breakdown by City

NEW YORK
NEWYORK 12 40 93 83 00 29 48
NEWYORK 20 89 85 01 92 30 93

BOSTON
BOSTON 20 102 94 29 10 48 29
BOSTON 50 152 54 39 15 43 59

etc.

Thanks!

--

Dave Peterson