Hi Rik,

Thank you very much for your input. I shall do my utmost to get it working.
--
Jamflam

Science Geek


"Rik_UK" wrote:

Hi Jamflam

Assuming the tables start in cell A1, the code required in a module of the
workbook could be:

Sub Macro1()
Sheets("Sheet1").Range("A1").CurrentRegion.AutoFil ter Field:=3,
Criteria1:="doctor "
Sheets("Sheet1").Range("A1").CurrentRegion.Copy
Sheets("Sheet2").Paste (Sheets("Sheet2").Range("A1"))
Sheets("Sheet1").Range("A1").CurrentRegion.AutoFil ter
Sheets("Sheet2").Range("A1").Select
End Sub

Good luck!

Rik_UK


"Jamflam" wrote:

Imagine this is Sheet 1

NAME EYES JOB TRAN. EYES
----------------------------------------------
John blue doctor car blond
Sarah green nurse bike brown
Dave brown doctor walk grey
Mike blue nurse car blonde

I would like Excel to automatically select all rows/info of doctors and copy
it to Sheet 2. So sheet 2 would be:

NAME EYES JOB TRAN. EYES
----------------------------------------------
John blue doctor car blond
Dave brown doctor walk grey


Please help me!
From,
Jamflam

Science Geek