Hi Scott;

This should do it:

Sub try()

HomeSheet = ActiveSheet.Name


'Go to Sheet2 and prepare it to receive the coping
Sheets("Sheet2").Select
Range("A1").Select

'Go back to the original sheet
Sheets(HomeSheet).Select


Range("I1").Select
While Not ActiveCell = ""
If ActiveCell = "E" Or ActiveCell = "M" Then

Range(Cells(ActiveCell.Row, 1), Cells
(ActiveCell.Row, 7)).Copy

Sheets("Sheet2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets(HomeSheet).Select



End If
ActiveCell.Offset(1, 0).Select
Wend
End Sub


Thanks,

Greg


-----Original Message-----

Simon,

I only what to search in one column which is "I". Then
if "E" or "M" is
found in that column then I want to Select columns "A to
G" in that same
row. But I want to do this to the entire sheet with one
click because
there are multiple instances within a workbook. I thought
of using a IF
statement and narrow the search only to column "I".
Thanks for the
help.

Scott


--

----------------------------------------------------------
--------------
's Profile:
http://www.excelforum.com/member.php?
action=getinfo&userid=16279
View this thread:
http://www.excelforum.com/showthread...hreadid=276840

.