Macro using advanced Filter

[howard]

I can manually use advanced filter to extract my data m, but when trying to write code to extract the data, only the headings are extracted

Option Explicit

 Sub Extract_Data()
Dim LR As Long
With Sheets("Extracted Data")
LR = Cells(.Rows.Count, "A").End(xlUp).Row
 .Range("A1:G" & LR).ClearContents

  Sheets("Imported Data").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("'Extracted Data'!Criteria"), CopyToRange:=.Range("A1") _
, Unique:=False
End With
End Sub

Kindly check my code and amend

 

[DanteAmor]

The code works.
You can put a generic sample of your data: sheet "Imported Data" and of the data in the range "Criteria". Use XL2BB tool.

By the way, the code can be simplified in this way:

Sub Extract_Data()
  With Sheets("Extracted Data")
    .Range("A1:G" & .Cells(.Rows.Count, "A").End(3).Row).ClearContents
    Sheets("Imported Data").Columns("A:G").AdvancedFilter 2, .Range("Criteria"), .Range("A1")
  End With
End Sub

 

[Fluff]

Come on Howard you know the rules, especially regarding cross posting.

 

[howard]

I do know the rules, but guinely forgot to show the link, which I have now posted below

Advanced Filter to extracted data for a date range [SOLVED]

I have tried to set up an advanced filter to extract data from sheet "Imported Data" to sheet "extracted Data" to I have set up the criteria in sheet "Extracted Data" as following as I need to extract a particular code with a date range as well as a value within the date range Code...

www.excelforum.com

 

[howard]

Hi Dante

Thanks for the modified code, but still not extracting the data

See link to my file on Drop Box

Macro to extract Data Using Advanced Filter with Multiple Date Ranges.xlsm

 

[Fluff]

Thanks ?

 

[DanteAmor]

Normally we don't download the files, you could put an example, use XL2BB tool, I want to see how the data is in the sheet and how are the criteria.

 

[howard]

Hi Dante


See sheet where I want data extracted "Extracted Data"

Macro to extract Data Using Advanced Filter with Multiple Date Ranges.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Stock No.	Invoice	Type	Code	Description	Date	Value											Code	Date	Date	Value
2																		ABL-1	>=08/03/2020	<=11/03/2020	<=84877
3
Extracted Data

Source Sheet

Macro to extract Data Using Advanced Filter with Multiple Date Ranges.xlsm
	A	B	C	D	E	F	G
1	Stock No.	Invoice	Type	Code	Description	Date	Value
2	AFAJXXMJWJKE02962	16851	Replenishment	ABL-1	Shirts Blue S	06/03/2020	37733.00
3	AFAJXXMJWJKD88595	17026	Replenishment	KLP-10	Shirts Blue S	06/03/2020	38928.00
4	MAJAXXMRKAKL74311	17037	Replenishment	ABL-2	Shirts Blue S	07/03/2020	15623.00
5	MAJGXXMTKGKS29137	17053	Replenishment	ABL-2	Shirts Blue S	07/03/2020	11842.00
6	AFAPXXMJ2PKE01447	17212	Replenishment	KLP-10	Shirts W M	07/03/2020	21650.00
7	AFANXXMJ2NKY22321	17223	Replenishment	ABL-1	Shirts W M	07/03/2020	21781.00
8	WF01XXERK1KS60070	17307	Replenishment	KLP-10	Shirts W M	07/03/2020	18579.00
9	WF01XXERK1KS60540	17310	Replenishment	KLP-10	Shirts W M	07/03/2020	19493.00
10	AFAJXXMJWJKS31316	17311	Replenishment	ABL-1	Shirts W M	08/03/2020	35976.00
11	AFAPXXMJ2PKY17441	17313	Replenishment	ABL-1	Shirts W M	09/03/2020	25159.00
12	MAJGXXMTKGKU42989	17314	Replenishment	ABL-1	Shirt B M	10/03/2020	11900.00
13	MAJGXXMTKGKU43229	17315	Replenishment	ABL-1	Shirt B M	11/03/2020	11842.00
14	MAJGXXMTKGKS27194	17316	Replenishment	KLP-10	Shirt B M	12/03/2020	13591.00
15	MAJGXXMTKGKS19511	17317	Replenishment	KLP-10	Shirt B M	13/03/2020	13378.00
16	MAJGXXMTKGKL03543	17318	Replenishment	KLP-11	Shirt B M	14/03/2020	13321.00
17	MAJAXXMRKAKJ34461	17319	Replenishment	ABL-1	Shirt B M	15/03/2020	16711.00
18	WF01XXERK1KS59713	17323	Replenishment	KLP-10	Shirt B M	16/03/2020	18579.00
Imported Data

 

[DanteAmor]

In the case of dates, in the advanced filter, you must use the number that excel uses as the date.
One way to get the number of a date is with the DATEVALUE formula. In addition, the criteria for date must go in a formula, as shown below:

Dante Amor
	R	S	T	U
1	Code	Date	Date	Value
2	ABL-1	>=43898	<=43901	<=84877
Extracted Data

Cell Formulas
Range	Formula
S2	S2	=">="&DATEVALUE("08/03/2020")
T2	T2	="<="&DATEVALUE("11/03/2020")

 

[howard]

Thanks for the advise Dante