Autofill datasheet using filter
 

bump, anyone? :confused:


--
Thrain
------------------------------------------------------------------------
Thrain's Profile: http://www.excelforum.com/member.php...o&userid=29192
View this thread: http://www.excelforum.com/showthread...hreadid=490344

Autofill datasheet using filter
 

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

InsertNameDefine
Names in workbook: Sheet2!rngDest
Refers to: =Sheet2!$A$1:$H$1

I1: f$nXwX
I2: f$n4w4

InsertNameDefine
Names in workbook: Sheet2!rngCriteria
Refers to: =Sheet2!$J$1:$J$2

Now for the tricky part...still on Sheet2:
InsertNameDefine
Names in workbook: Sheet1!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:
ToolsMacroMacros (or [Alt]+[F8])
Select and run: PullMatchingData

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

Does that help?
Ron


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=490344

Autofill datasheet using filter
 

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

Ron


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=490344

Autofill datasheet using filter
 

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


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=490344