Automatic selecting and sharing of data accross worksheets

J

Jamflam

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,
 
R

Rik_UK

Also you posted in Programming - my reply...

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.AutoFilter Field:=3,
Criteria1:="doctor "
Sheets("Sheet1").Range("A1").CurrentRegion.Copy
Sheets("Sheet2").Paste (Sheets("Sheet2").Range("A1"))
Sheets("Sheet1").Range("A1").CurrentRegion.AutoFilter
Sheets("Sheet2").Range("A1").Select
End Sub

Good luck!

Rik_UK
 
A

Ashish Mathur

Hi,

You may also use advanced filters. On sheets 2, type Job in cell B2 and
doctor in cell B3. Now click on cell B5 and go to Data > Filter > Advanced
Filter > Copy to another location. In the list range, select the range of
sheet 1 (including the header row). In the criteria range, select B2:B3 os
sheet2. In the copy to cell, select any blank cell on sheet2

Hope this helps.
 
J

Jamflam

Ashish (Great name)

Thank you very much for your help. I WILL endeavour to get this to work.
--
Jamflam

Science Geek


Ashish Mathur said:
Hi,

You may also use advanced filters. On sheets 2, type Job in cell B2 and
doctor in cell B3. Now click on cell B5 and go to Data > Filter > Advanced
Filter > Copy to another location. In the list range, select the range of
sheet 1 (including the header row). In the criteria range, select B2:B3 os
sheet2. In the copy to cell, select any blank cell on sheet2

Hope this helps.
Click to expand...
 
A

Ashish Mathur

Thank you. Hope the solution works

--
Regards,

Ashish Mathur
Microsoft Excel MVP

Jamflam said:
Ashish (Great name)

Thank you very much for your help. I WILL endeavour to get this to work.
Click to expand...
 
J

Jamflam

Rik,

Thanks again!
--
Jamflam

Science Geek


Rik_UK said:
Also you posted in Programming - my reply...

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.AutoFilter Field:=3,
Criteria1:="doctor "
Sheets("Sheet1").Range("A1").CurrentRegion.Copy
Sheets("Sheet2").Paste (Sheets("Sheet2").Range("A1"))
Sheets("Sheet1").Range("A1").CurrentRegion.AutoFilter
Sheets("Sheet2").Range("A1").Select
End Sub

Good luck!

Rik_UK
Click to expand...
 
Top