Better to use the autofilter feature than to step through:

With Sheets("Sheet1").Range("A1").CurrentRegion
.AutoFilter Field:=1, Criteria1:=Worksheets("Sheet2").Range("A1").Value
.AutoFilter Field:=2, Criteria1:=Worksheets("Sheet2").Range("B2").Value
.AutoFilter Field:=3, Criteria1:=Worksheets("Sheet2").Range("C2").Value
.AutoFilter Field:=4, Criteria1:=Worksheets("Sheet2").Range("D2").Value
.AutoFilter Field:=5, Criteria1:=Worksheets("Sheet2").Range("E2").Value
.AutoFilter Field:=6, Criteria1:=Worksheets("Sheet2").Range("F2").Value
.Columns(7).SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet2").Range("G2")
.AutoFilter
End With

HTH,
Bernie
MS Excel MVP


"damorrison" wrote in message
ups.com...
I use this one code to find an item with six different criteria, is
there a better way to find an item with multiple criteria?

Sub Button1_Click()
Sheets("Sheet2").Range("G:G").ClearContents
Application.ScreenUpdating = False
Sheets("Sheet1").Range("A1").Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value = Worksheets("Sheet2").Range("A1") Then
If ActiveCell.Offset(0, 1).Value = Worksheets("Sheet2").Range("B2")
Then
If ActiveCell.Offset(0, 2).Value = Worksheets("Sheet2").Range("C2")
Then
If ActiveCell.Offset(0, 3).Value = Worksheets("Sheet2").Range("D2")
Then
If ActiveCell.Offset(0, 4).Value = Worksheets("Sheet2").Range("E2")
Then
If ActiveCell.Offset(0, 5).Value = Worksheets("Sheet2").Range("F2")
Then
Sheets("Sheet2").Range("G6000").End(xlUp).Offset(1 , 0) =
ActiveCell.Offset(0, 6)
End If
End If
End If
End If
End If
End If
ActiveCell.Offset(1, 0).Select
Loop


End Sub