The simple answer is: Don't loop. Use Excel's built-in functionality to find the values in one
swoop, and copy them en-masse to a new workbook. This assumes that your simple data table is
contiguous (no entirely blank rows within the table).

_IF_ you wanted to move each one to a separate workbook, then post back, and we can modify the code
to loop through the found cells, adding a new workbook or worksheet for each.

Sub Macro1()
Dim mySht As Worksheet
Dim myRange As Range

' Change the line below to the sheet with your table, and a cell in the column with the P's
' There are lots of different ways to do this, but this is simple, for example purposes
Set myRange = Worksheets("Data").Range("D4")

Set mySht = Worksheets.Add
mySht.Name = "Extract"

With Intersect(myRange.EntireColumn, myRange.CurrentRegion)
.AutoFilter Field:=4 - .Cells(1).Column + 1, Criteria1:="P"
.SpecialCells(xlCellTypeVisible).EntireRow.Copy _
mySht.Range("1:1")
.AutoFilter
End With

mySht.Move

End Sub


HTH,
Bernie
MS Excel MVP


"dsi" wrote in message
oups.com...
I want to iterate throught the rows of a simple table, checking for the
value "P" in a specified column of each row, once I find "P" I want to
copy the row to a new workbook.

concatenating many (4) of the following together does exactly what I
need (except it won't handle the case of the engineers adding more than
5 similar parts)
If (Cells(c.Row, colPrimary).Value < "P") Then
Set c = Cells(c.Row + 1, c.Column)
End If

The following do while...loop structure doesn't do what I need and
breaks MsgBox statements that appear before the loop in the code.
Do Until (Cells(c.Row, colPrimary).Value = "P")
Set c = Cells(c.Row + 1, c.Column)
Loop

do you need more info?