Autofill datasheet using filter

[Thrain]

MASTER SHEET:

f$nXwX adj NIT HAND BOARD P PRWIN PRTIE
f$n4w4 2.00 100k AcAd AhAs2c 1 1.00 0.00
f$n4w4 1.50 100k AcAd AhAs2c 2 1.00 0.00
f$n4w2 1.33 100k AcAd AhAs2c 3 1.00 0.00
f$n4w2 1.25 100k AcAd AhAs2c 4 1.00 0.00
f$n4w2 1.20 100k AcAd AhAs2c 5 1.00 0.00
f$n4w3 1.17 100k AcAd AhAs2c 6 1.00 0.00
f$n4w4 1.14 100k AcAd AhAs2c 7 1.00 0.00
f$n4w3 1.12 100k AcAd AhAs2c 8 1.00 0.00
f$n4w4 2.00 100k Ac2c 3c9cQc 1 0.98 0.00


I want to create a sheet that lists all the rows with f$n4w4, like this:

f$nXwX adj NIT HAND BOARD P PRWIN PRTIE
f$n4w4 2.00 100k AcAd AhAs2c 1 1.00 0.00
f$n4w4 1.50 100k AcAd AhAs2c 2 1.00 0.00
f$n4w4 1.14 100k AcAd AhAs2c 7 1.00 0.00
f$n4w4 2.00 100k Ac2c 3c9cQc 1 0.98 0.00

I cannot figure out how to do this programatically ;/ any help is greatly appreciated. thank you.

[Thrain]

bump, anyone?

[Ron Coderre]

Ok...Here's something to try:

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

So....
Using Sheet2:
A1: f$nXwX
B1: adj
C1: NIT
D1: HAND
E1: BOARD
F1: P
G1: PRWIN
H1: PRTIE

Insert>Name>Define
Names in workbook: Sheet2!rngDest
Refers to: =Sheet2!$A$1:$H$1

J1: f$nXwX
J2: f$n4w4

Insert>Name>Define
Names in workbook: Sheet2!rngCriteria
Refers to: =Sheet2!$J$1:$J$2

Now for the tricky part...still on Sheet2:
Insert>Name>Define
Names in workbook: Sheet2!rngSource
Refers to: =Sheet1!$A$1:$H$10

(Notice: you are on Sheet2, but 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.

Next: In a general vba module, enter this code:

Option Explicit
Sub PullMatchingData()
Range("Sheet2!rngSource").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Range("Sheet2!rngCriteria"), _
CopyToRange:=Range("Sheet2!rngDest"), _
Unique:=False
End Sub

To run the code:
Tools>Macro>Macros (or [Alt]+[F8])
Select and run: PullMatchingData

Change the value of J2 to f$n4w2 and run it again.

Does that help?
Ron

[Ron Coderre]

References to I1 and I2 should be replaced with J1 and J2

Ron

[Ron Coderre]

Names in workbook: Sheet1!rngSource

SHOULD BE:
Names in workbook: Sheet2!rngSource

(Changed Sheet1 to Sheet2)

I've got to do more copy/paste and less type-type-type. (sheesh)

Ron