I am using the Data/Advance Filter. How can I have the filtered results
placed on another sheet automatically (without copying from the active sheet).

Thanks.

zhj23

Here's an approach to try if you'll be doing that regularly:

Assumptions:
Sheet1 contains your data in cells A1:B10
Sheet2 is where you want the extracted data to be displayed

Using Sheet2:
A1: EmpID
B1: Age

InsertNameDefine
Names in workbook: Sheet2!Extract
Refers to: =Sheet2!$A$1:$B$1

I1: EmpID
I2: 24

InsertNameDefine
Names in workbook: Sheet2!Criteria
Refers to: =Sheet2!$I$1:$I$2

Still using Sheet2:
InsertNameDefine
Names in workbook: Sheet2!Database
Refers to: =Sheet1!$A$1:$B$10

(Notice: you are on Sheet2, and creating a Sheet2-level range name, but
the referenced range is on Sheet1)

The reason: An advanced filter cannot SEND data to another sheet, but
it can PULL data from another sheet.

Now...set up the Advanced Data Filter:
<Data<Filter<Advanced Filter
Select: Copy to another location
List Range: (press F3 and select Database)
Criteria Range: (press F3 and select Criteria)
Copy To: (press F3 and select Extract)
Click [OK]

Note: if you want to run that Advanced Data Filter repeatedly,
you'll need to re-select Database each time
.....OR...if you're feeling a bit ambitious...

You can build a simple macro to automatically re-run the filter:
Press [Alt]+[F11] to open the VBA editor
Right click on the VBA Project folder for your workbook
Select: InsertModule

Then, copy/paste this code into that module:

'---Start of Code-------
Option Explicit
Sub PullMatchingData()
Range("Sheet2!Database").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Range("Sheet2!Criteria"), _
CopyToRange:=Range("Sheet2!Extract"), _
Unique:=False
End Sub
'---Start of Code-------

To run the code:
ToolsMacroMacros (or [Alt]+[F8])
Select and run: PullMatchingData

To test, change the value of I2 and run it again.



Does that help?
***********
Regards,
Ron

XL2002, WinXP


"zhj23" wrote:

I am using the Data/Advance Filter. How can I have the filtered results
placed on another sheet automatically (without copying from the active sheet).

Thanks.

zhj23

Thanks for your effort to explain in details. I will definitely try both
methods.

zhj23

"Ron Coderre" wrote:

Here's an approach to try if you'll be doing that regularly:

Assumptions:
Sheet1 contains your data in cells A1:B10
Sheet2 is where you want the extracted data to be displayed

Using Sheet2:
A1: EmpID
B1: Age

InsertNameDefine
Names in workbook: Sheet2!Extract
Refers to: =Sheet2!$A$1:$B$1

I1: EmpID
I2: 24

InsertNameDefine
Names in workbook: Sheet2!Criteria
Refers to: =Sheet2!$I$1:$I$2

Still using Sheet2:
InsertNameDefine
Names in workbook: Sheet2!Database
Refers to: =Sheet1!$A$1:$B$10

(Notice: you are on Sheet2, and creating a Sheet2-level range name, but
the referenced range is on Sheet1)

The reason: An advanced filter cannot SEND data to another sheet, but
it can PULL data from another sheet.

Now...set up the Advanced Data Filter:
<Data<Filter<Advanced Filter
Select: Copy to another location
List Range: (press F3 and select Database)
Criteria Range: (press F3 and select Criteria)
Copy To: (press F3 and select Extract)
Click [OK]

Note: if you want to run that Advanced Data Filter repeatedly,
you'll need to re-select Database each time
....OR...if you're feeling a bit ambitious...

You can build a simple macro to automatically re-run the filter:
Press [Alt]+[F11] to open the VBA editor
Right click on the VBA Project folder for your workbook
Select: InsertModule

Then, copy/paste this code into that module:

'---Start of Code-------
Option Explicit
Sub PullMatchingData()
Range("Sheet2!Database").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Range("Sheet2!Criteria"), _
CopyToRange:=Range("Sheet2!Extract"), _
Unique:=False
End Sub
'---Start of Code-------

To run the code:
ToolsMacroMacros (or [Alt]+[F8])
Select and run: PullMatchingData

To test, change the value of I2 and run it again.



Does that help?
***********
Regards,
Ron

XL2002, WinXP


"zhj23" wrote:

I am using the Data/Advance Filter. How can I have the filtered results
placed on another sheet automatically (without copying from the active sheet).

Thanks.

zhj23