advanced filter doesn't work for me

[Hasson]

hello
I have sheet2 contains values in column B and sheet1 contains data from A:F . I want using advanced filter . so the result in sheet2 based on column B should match with column A in sheet1 and brings filterd data in row8 .

Sub AdvFilter()
  Dim rCrit As Range
  
 Dim ws As Worksheet
    Set ws = Sheets("sheet2")
  Set rCrit = ws.Range("B1:B2")
 With ThisWorkbook.Worksheets("sheet1")
  Range("A1", Range("E" & Rows.Count).End(xlUp)).Resize(, 5).AdvancedFilter 1, rCrit, Sheets("sheet2").Cells(8)
End With

End Sub

any chance to fixing this code

 

[HaHoBe]

Hi Hasson,

you talk about data in Column A to F (6 Columns) but only refer to 5 columns in the code. And the 1 you use is for xlFilterInPlace, the 2 is for xlFilterCopy.

You would need to adjust the ranges to suit:

Sub AdvFilter()
Dim rCrit As Range
Dim ws As Worksheet

With ThisWorkbook
  Set ws = .Worksheets("sheet2")
  Set rCrit = ws.Range("B1:B2")
  With .Worksheets("sheet1")
    .Range("A1", .Range("E" & .Rows.Count).End(xlUp)).AdvancedFilter xlFilterCopy, rCrit, ws.Range("B8")
  End With
End With

Set rCrit = Nothing
Set ws = Nothing
End Sub

And to be honest: maybe use xl2BB for getting us an image if what you have and what you want. Because based on your descrition a different result may be wanted.

Cells(8) is H1 on my system, not Row 8.

Ciao,
Holger

 

[RoryA]

Your With block isn't currently doing anything. It should read:

With ThisWorkbook.Worksheets("sheet1")
  .Range("A1", .Range("E" & .Rows.Count).End(xlUp)).Resize(, 5).AdvancedFilter 2, rCrit, Sheets("sheet2").Cells(8)
End With

Also, you had 1 as the action, which is to filter in place.

 

[Hasson]

@HaHoBe thanks but it copy all of data without filter based on B1:B2

maybe use xl2BB for getting us an image if what you have and what you want. Because based on your descrition a different result may be wanted.

REPORT.xlsm
	A	B	C	D	E	F
1	ITEM	ID	QTY1	QTY2	QTY3	BALANCE
2	1	TT/W-1 MM CLA1 23M-1 IT	500			500
3	2	QQW-2 TH NM-1 CLA2 VBG L CHI				0
4	3	QQW-3 CV CLA3 TAI	700	33		733
5	4	QQW-4 M*12.5 CLA4 TR	200		12	188
6	5	QQW-5 CLA5 EG	300			300
7	6	MMR12/100 AS-1000/1 TMR	12	0	0	12
8	7	QQW-6 M230 TU	1			1
9	8	QQW-7 S** CLA7 US	1400		1	1399
10	9	QQW-8 CLA8 UK				0
11	10	QQW-9 CLA9 N BR	1600		1	1599
12	11	QQW-10 BN CLA10 IT	800	0	0	800
13	12	QQW-11 LVD CH	900			900
14	13	BB12 QQW-12 CLA12 JA	1000		11	989
sheet1

Cell Formulas
Range	Formula
F2:F14	F2	=C2+D2-E2

REPORT.xlsm
	B
1	TT/W-1 MM CLA1 23M-1 IT
2	QQW-2 TH NM-1 CLA2 VBG L CHI
sheet2

result

REPORT.xlsm
	A	B	C	D	E	F
1		TT/W-1 MM CLA1 23M-1 IT
2		QQW-2 TH NM-1 CLA2 VBG L CHI
3
4
5
6
7
8	ITEM	ID	QTY1	QTY2	QTY3	BALANCE
9	1	TT/W-1 MM CLA1 23M-1 IT	500			500
10	2	QQW-2 TH NM-1 CLA2 VBG L CHI				0
sheet2

Cell Formulas
Range	Formula
F9:F10	F9	=C9+D9-E9

 

[RoryA]

B1 needs to be a column header, not a filter value. So you'll need to move those two down, add the column header "ID" to B1, then expand the criteria range to use B1:B3.

 

[Hasson]

@RoryA you wanna to say the advanced filter start from the header?
actually this is the first time using advanced filter I no know the rules , sorry !

 

[RoryA]

The first cell in the criteria range should be the header of the column you are trying to filter by - ID in this case.

 

[HaHoBe]

Hi Hasson,

sheet2 needs to look like Rory stated

Mappe1
	A	B	C	D	E	F
1		ID
2		TT/W-1 MM CLA1 23M-1 IT
3		QQW-2 TH NM-1 CLA2 VBG L CHI
4
5
6
7
8	ITEM	ID	QTY1	QTY2	QTY3	BALANCE
9	1	TT/W-1 MM CLA1 23M-1 IT	500			500
10	2	QQW-2 TH NM-1 CLA2 VBG L CHI				0
Sheet2

And the code should be modified to this

Sub AdvFilter()
Dim rCrit As Range
Dim ws As Worksheet

With ThisWorkbook
  Set ws = .Worksheets("sheet2")
  Set rCrit = ws.Range("B1:B3")
  With .Worksheets("sheet1")
    .Range("A1", .Cells(.Range("A" & .Rows.Count).End(xlUp).Row, "F")).AdvancedFilter xlFilterCopy, rCrit, ws.Range("A8")
  End With
End With

Set rCrit = Nothing
Set ws = Nothing
End Sub{/code]
CIao,
Holger

 

[Hasson]

@HaHoBe thanks but it shows problem if I change the items to filter another doesn't replace it keeping old data, how can I fix it,please?

 

[HaHoBe]

Hi Hasson,

the use of Advanced Filter should always clear the data and add the newly imported one. The data will stay on the sheet as long as you run the macro again or you delete the output. One way to go may be use this code behind the worksheet:

Private Sub Worksheet_Deactivate()
Range("A8").CurrentRegion.ClearContents
End Sub

which will delete the result when you leave the sheet (activate another sheet).

Ciao,
Holger