One way is a VBA loop that looks through column A and copies the
appropriate rows. Paste this code in a standard module (see
http://www.rondebruin.nl/code.htm for placement assistance). This code
assumes the identified record is in H1001. Replace "myBook" with the
name of the actual workbook you are copying the rows to.
Sub CopyRecords()
Dim rng As Excel.Range
Dim cell As Excel.Range
Dim NewBook As Excel.Workbook
Set NewBook = Workbooks("myBook")
Set rng = Range("A2", Range("A" &
Rows.Count).End(xlUp)).SpecialCells(xlCellTypeCons tants, 2) 'text
values only
For Each cell In rng
If cell.Value = "No" Then
If (cell.Offset(0,3).Value = Cells(1001,8)) And _
(cell.Offset(0,4).Value = Cells(1001,8)) And _
(cell.Offset(0,6).Value = Cells(1001,8)) Then
cell.EntireRow.Copy _
NewBook.Sheets(1).Range("A65536").End(xlUp).Offset (1,0)
End If
End If
Next cell
End Sub
This is air code so please test it first. Even faster is a VBA loop
that uses the .Find method, for example:
http://www.ozgrid.com/VBA/VBALoops.htm
HTH,
JP
On Apr 2, 6:32*pm, Helen wrote:
I don't understand your formula. *It looks like you're comparing data in two
columns for the same record (D1=E1, etc.).
Perhaps I didn't explain my problem adequately. *I have a spreadsheet with
over a thousand records. *Each record has data in 50 columns. *I want to send
to a second spreadsheet every record (1 through 1000) that matches the data
in Columns D, E, and G of record 1001.
It seems reasonable to me that a function exists that will allow me to do
this other than manually.