Automatic selecting and sharing of data accross worksheets

  • Thread starter Thread starter Jamflam
  • Start date Start date
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,
 
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
 
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.
 
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...
 
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...
 
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...
 
Back
Top