Macro Help

[sbs]

Simon,

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

Scot

 

[GJones]

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