Smart Auto filter VBA

[Kishan]

Hello,

Example sheet1 contain data I need Smart Auto filter VBA which select “Field and Criteria” from sheet2 and apply filter to sheet1

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Date	P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14
2	Date	P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14
3	01/12/2013	2	X	2	1	1	1	X	1	1	1	2	X	1	1
4	02/12/2013	2	2	X	1	2	1	X	X	2	1	2	1	2	X
5	03/12/2013	1	1	1	1	1	X	1	X	1	1	X	X	1	2
6	04/12/2013	1	1	1	2	1	1	1	2	X	1	1	1	X	1
7	05/12/2013	1	X	1	1	X	1	2	X	1	1	1	1	1	X
8	06/12/2013	1	1	1	2	2	X	1	1	1	X	1	1	1	X
9	07/12/2013	1	1	X	1	1	1	1	2	1	X	1	1	X	1
10	08/12/2013	1	2	1	1	1	1	2	1	1	X	1	1	1	2
11	09/12/2013	2	2	X	2	1	1	2	X	2	1	1	X	1	2
12	10/12/2013	X	1	1	1	1	2	2	1	1	2	1	X	1	1
13	11/12/2013	1	2	1	1	X	1	1	1	1	X	X	1	X	X
14	12/12/2013	1	1	2	X	2	2	1	2	X	1	1	X	2	1
15	13/12/2013	1	X	1	X	1	2	2	1	1	1	1	2	2	2
16	14/12/2013	X	X	1	1	2	1	1	2	2	2	X	1	1	1
17	15/12/2013	2	2	2	2	2	X	1	1	1	2	X	2	X	1
18	16/12/2013	X	1	1	1	X	X	X	1	1	1	X	1	2	2
19	17/12/2013	2	2	X	1	2	1	2	1	2	1	X	1	1	X
20	18/12/2013	X	1	1	2	1	1	2	1	X	2	X	2	2	1
21	19/12/2013	1	1	1	2	1	2	X	2	2	X	1	2	1	1
22	20/12/2013	2	1	1	1	X	2	1	1	X	1	X	1	2	2
Sheet1

Sheet2 data looks as shown below
Example1
Smart VBA…. can give these following options to filter data
1^st Select Field from column ‘A’ header for example A1= P2|P5|P7 So Field:=2, Field:=5, Field:=7
2^nd VBA gives input option to select any cell in column A2 To down for Criteria selection for example if we select A5 = X|X|2 So Criteria1:=X, Criteria1:=X, Criteria1:=1
Filter selection would be as shown below
AutoFilter Field:=2, Criteria1:=X
AutoFilter Field:=5, Criteria1:=X
AutoFilter Field:=7, Criteria1:=1

Book1
	A
1	P2|P5|P7
2	X|1|X
3	2|2|X
4	1|1|1
5	X|X|2
6	1|2|1
7	2|1|2
8	1|1|2
9	2|X|1
10	X|1|2
11	X|2|1
12	2|2|1
13	1|X|X
14	2|2|2
15	1|1|X
16	1|X|1
Sheet2

Sheet2 data looks as shown below
Example2
Smart VBA…. can give these following options to filter data
1^st Select Field from column ‘A’ header for example A1= P3|P7|P10 So Field:=3, Field:=7, Field:=10
2^nd VBA gives input option to select any cell in column A2 To down for Criteria selection for example if we select A2 = 2|X|1 So Criteria1:=2, Criteria1:=X, Criteria1:=1
Filter selection would be as shown below
AutoFilter Field:=3, Criteria1:=2
AutoFilter Field:=7, Criteria1:=X
AutoFilter Field:=10, Criteria1:=1

Book1
	A
1	P3|P7|P10
2	2|X|1
3	X|X|1
4	1|1|1
5	1|2|1
6	1|1|X
7	X|1|X
8	1|2|X
9	X|2|1
10	1|2|2
11	2|1|1
12	1|1|2
13	2|1|2
14	1|X|1
15	1|X|X
Sheet2

Sheet2 data looks as shown below
Example3
Smart VBA…. can give these following options to filter data
1^st Select Field from column ‘A’ header for example A1= P1|P7|P12|P14So Field:=1, Field:=7, Field:=12 , Field:=14
2^nd VBA gives input option to select any cell in column A2 To down for Criteria selection for example if we select A3 = 2|X|1|X So Criteria1:=2, Criteria1:=X, Criteria1:=1, Criteria1:=X
Filter selection would be as shown below
AutoFilter Field:=1, Criteria1:=2
AutoFilter Field:=7, Criteria1:=X
AutoFilter Field:=12, Criteria1:=1
AutoFilter Field:=14, Criteria1:=X

Book1
	A
1	P1|P7|P12|P14
2	2|X|X|1
3	2|X|1|X
4	1|1|X|2
5	1|1|1|1
6	1|2|1|X
7	1|1|1|X
8	1|2|1|2
9	2|2|X|2
10	X|2|X|1
11	1|1|X|1
12	1|2|2|2
13	X|1|1|1
14	2|1|2|1
15	X|X|1|2
16	2|2|1|X
17	X|2|2|1
18	1|X|2|1
19	2|1|1|2
Sheet2

Does it is feasible

Using Excel Version 2000

Thank You

Regards,
Kishan

 

[Kishan]

Example1
Smart VBA…. can give these following options to filter data
1^st Select Field from column ‘A’ header for example A1= P2|P5|P7 So Field:=2, Field:=5, Field:=7
2^nd VBA gives input option to select any cell in column A2 To down for Criteria selection for example if we select A5 = X|X|2 So Criteria1:=X, Criteria1:=X, Criteria1:=1
Filter selection would be as shown below
AutoFilter Field:=2, Criteria1:=X
AutoFilter Field:=5, Criteria1:=X
AutoFilter Field:=7, Criteria1:=1

	A
1	P2|P5|P7
2	X|1|X
3	2|2|X
4	1|1|1
5	X|X|2
6	1|2|1
7	2|1|2
8	1|1|2
9	2|X|1
10	X|1|2
11	X|2|1
12	2|2|1
13	1|X|X
14	2|2|2
15	1|1|X
16	1|X|1

<tbody>
</tbody>

Sheet2​

Hi,

Re reading my query I found in given example1 “Criteria” is not define correctly
Example1
Smart VBA…. can give these following options to filter data
1^st Select Field from column ‘A’ header for example A1= P2|P5|P7 So Field:=2, Field:=5, Field:=7
2^nd VBA gives input option to select any cell in column A2 To down for Criteria selection for example if we select A5 = X|X|2 So Criteria1:=X, Criteria1:=X, Criteria1:=2
Filter selection would be as shown below
AutoFilter Field:=2, Criteria1:=X
AutoFilter Field:=5, Criteria1:=X
AutoFilter Field:=7, Criteria1:=2

Book1
	A
1	P2|P5|P7
2	X|1|X
3	2|2|X
4	1|1|1
5	X|X|2
6	1|2|1
7	2|1|2
8	1|1|2
9	2|X|1
10	X|1|2
11	X|2|1
12	2|2|1
13	1|X|X
14	2|2|2
15	1|1|X
16	1|X|1
Sheet2

This filter will make life easier

Please help

Regards,
Kishan

 

[Kishan]

Bump