Great Difficulty Getting Results From VBA Advanced Filter

[Ark68]

I am simply trying to filter data according to my advanced criteria range in worksheet "ws_fac" (in 2nd workbook) and copy it over to a range starting at AN3 in worksheet "ws_lists" (of 1st primary code holding workbook)

Here is the code I'm struggling with:

If ws_fac.FilterMode Then ws_fac.ShowAllData                                              'turn off any filtering if data is filtered
ws_lists.Range("AN3").CurrentRegion.Offset(1).ClearContents                      'clear the destination range (filtered data to be copied here)
Set rgData = ws_fac.Range("A1").CurrentRegion                                           'define the source range (all data in ws_fac including header row 1)
Set rgCriteriaRange = ws_lists.Range("AI2:AJ3").CurrentRegion                   'criteria range (AI3 AND AJ3)
Set rgCopyToRange = ws_lists.Range("AN3").CurrentRegion.Rows(1)          'destination target (ws_lists starting at AN3)
rgData.AdvancedFilter xlFilterCopy, rgCriteriaRange, rgCopyToRange        'apply filter
lrfaclst = ws_lists.Cells(ws_lists.Rows.Count, "AN").End(xlUp).Row                'get the value of the last row of copied data range
ws_lists.Range("AN" & lrfaclst + 1) = "Nothing suitable."                             'add a value to the end of the column of data

The line in red is giving me a "The extract range has a missing or invalid field name." I'm unsure why. The column headers match the reference headers in my data. All the columns in my source data have a header label, and no column is without a header label.

Here is the structure of my source data worksheet:

Facilities.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	hh FIELD (do not alter)	AN RESOURCE TYPE	CLASS	RESP	TYPE	UNIT	LIT	LABEL	Washroom	WR_Code	FAMILY	WP_Side	GROOM	ACT-PASS	CONFIG	GOALS	PARK	Address
2	hhhhhhhhhhhhhh	hhhhhhhhhh	B	BP	hhhh	1	N	hhhhhhhhhhhhhhhhh	N	x	BP		CRP	A			hhhh
3	llllllll	lllllllll	A	BP	llll	1	Y	lllllllllllllll	Y	x	BP		CRP	A			lllll
4	mmmmmmmmmmmmm	mmm	B	BP	Field	1	N	mmmmmmmmm	N	x	BP		na	A	NC	NA	mmm
5	mmmmmmmmmmmmm	mmm	A	BP	Field	2	Y	mmmmmmmmm	N	x	BP		na	A	S1 90m x 61.5m	NA	mmm
Facilities

Here is my criteria range:

SignatureSheets.xlsm
	AI	AJ
1	Advanced Filter1
2	ACT-PASS	LABEL
3	P	WP*
lists

And finally, the structure of my destination:

SignatureSheets.xlsm
	AN	AO
1	FacList
2	ACT-PASS	LABEL
3
4
5
6
lists

I don't yet quite understand VBA based advanced filtering so I'm struggling to figure out where I need to make corrections. Be very grateful for any one that can offer up some advice, solutions.

 

[Ark68]

Wonderful! Starting to get greater clarity on some of the functions I've been scared to use. Basic tutorials are good, but I find that sometimes my needs are unique and although the concept is the same, it's difficult to relate to adjust accordingly. Practice makes perfect!

Offset by rows moves the whole range area you are working with down by that no of rows.
So Range("A10:E13").Offset(5) becomes Range("A15:E18").

Perhaps still not grasping it, but wouldn't Range("A10:E13").Offset(5) become Range("A15:E13")? Or does just the one value of Offset change both the row and column by that same number?

 

[Alex Blakenburg]

A10:E15 is a block of cells 5 by 5.
Offset by 5 rows moves the whole block down 5 rows.
So you still have a block of 5 by 5 just 5 rows further down the sheet.
So just to get away from all the 5s, if the block was say 4x3 it would stay 4x3 just the offset no of rows further down

 

[Ark68]

Ahhh! Got it! I think it's that that got me all messed up when I've played with offset before.