Filter Sheet2 Data based on Sheet1 List Values

[pwill]

Hi can anyone help with a macro?

I have many hundreds of rows of Data on Sheet2 "A1:Z" with headers.
"A2:A" has 'Ref values' for each row of Data.

Sheet1 column "R5:R" has 'Ref values' added that change daily.

I want to Filter Sheet2 "A:Z" using "A2:A" as field, based on the Values entered on Sheet1

"R5:R" and then copy the filtered Data to last row on Sheet3

Sheet2 Data example

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1	Ref	Cnt							Ref	Cnt							Ref	Cnt							Ref	Cnt
2	1	2							1	18							1	31							1	32
3	1	2							1	18							1	31							1	32
4	1	2							1	18							1	31							1	32
5	2	6							2	7							2	14							2	16
6	2	6							2	7							2	14							2	16
7	2	6							2	7							2	14							2	16
8	3	5							3	24							3	30							3	33
9	3	5							3	24							3	30							3	33
10	3	5							3	24							3	30							3	33
11	4	2							4	5							4	10							4	24
12	4	2							4	5							4	10							4	24
13	4	2							4	5							4	10							4	24
14	5	18							5	21							5	22							5	33
15	5	18							5	21							5	22							5	33
16	5	18							5	21							5	22							5	33
17	6	9							6	21							6	29							6	32
18	6	9							6	21							6	29							6	32
19	6	9							6	21							6	29							6	32
20	7	20							7	25							7	27							7	31
21	7	20							7	25							7	27							7	31
22	7	20							7	25							7	27							7	31
23
24
25

<tbody>
</tbody>

If Sheet1 column "R5:R9" has values 1,2,4,6,7 then the copied filtered Data on Sheet3 would be

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1	Ref	Cnt							Ref	Cnt							Ref	Cnt							Ref	Cnt
2	1	2							1	18							1	31							1	32
3	1	2							1	18							1	31							1	32
4	1	2							1	18							1	31							1	32
5	2	6							2	7							2	14							2	16
6	2	6							2	7							2	14							2	16
7	2	6							2	7							2	14							2	16
8	4	2							4	5							4	10							4	24
9	4	2							4	5							4	10							4	24
10	4	2							4	5							4	10							4	24
11	6	9							6	21							6	29							6	32
12	6	9							6	21							6	29							6	32
13	6	9							6	21							6	29							6	32
14	7	20							7	25							7	27							7	31
15	7	20							7	25							7	27							7	31
16	7	20							7	25							7	27							7	31
17
18
19
20
21
22
23
24
25

<tbody>
</tbody>

Any help would be appreciated

regards

pwill
​

​

_{^{<strike>
</strike>}}​

 

[DanteAmor]

Hi can anyone help with a macro?

I have many hundreds of rows of Data on Sheet2 "A1:Z" with headers.
"A2:A" has 'Ref values' for each row of Data.

Sheet1 column "R5:R" has 'Ref values' added that change daily.

I want to Filter Sheet2 "A:Z" using "A2:A" as field, based on the Values entered on Sheet1

"R5:R" and then copy the filtered Data to last row on Sheet3


If Sheet1 column "R5:R9" has values 1,2,4,6,7 then the copied filtered Data on Sheet3 would be

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1	Ref	Cnt							Ref	Cnt							Ref	Cnt							Ref	Cnt
2	1	2							1	18							1	31							1	32
3	1	2							1	18							1	31							1	32
4	1	2							1	18							1	31							1	32
5	2	6							2	7							2	14							2	16
6	2	6							2	7							2	14							2	16
7	2	6							2	7							2	14							2	16
8	4	2							4	5							4	10							4	24
9	4	2							4	5							4	10							4	24
10	4	2							4	5							4	10							4	24
11	6	9							6	21							6	29							6	32
12	6	9							6	21							6	29							6	32
13	6	9							6	21							6	29							6	32
14	7	20							7	25							7	27							7	31
15	7	20							7	25							7	27							7	31
16	7	20							7	25							7	27							7	31
17

<tbody>
</tbody>

Any help would be appreciated
regards
pwill
​

​

​

You can check your example, on sheet 1, in cells R5:R9 the values ​​are 14,14,14,10,10.


Also tell me in which row the headings are on sheet1. The names of the headings of sheet1 are the same as those of sheet2.


After reviewing the R5:R9 range, can you explain, according to your example, what data should be copied?

 

[pwill]

Hi DanteAmor,

I didn't give an example for sheet1, the above example is the Data on sheet2 and the output on sheet3 if 'Sheet1 column "R5:R9" had values 1,2,4,6,7'

The headers are the same on sheet2 and sheet3
There are no headers on sheet1

The data that should be copied would be

Sheet2 "A2:Z" when applying the filter to 'column A or field 1 on sheet2 to only show the values from Sheet1

Sheet1 values

"R5" = 1

"R6" = 2​

​

​

​

"R7" = 4

"R8" = 6
"R9" = 7

hope that makes more sense?

regards​

​

 

[DanteAmor]

My mistake, I thought it was the sheet1.Try this

Sub Filter_with_multiple_conditions()
    Dim c As Range, n As Long, arr() As Variant
    For Each c In Sheets("Sheet1").Range("R5", Sheets("Sheet1").Range("R" & Rows.Count).End(xlUp))
        ReDim Preserve arr(n)
        arr(n) = c.Text
        n = n + 1
    Next
    Sheets("Sheet2").UsedRange.AutoFilter Field:=1, Criteria1:=arr, Operator:=xlFilterValues
    Sheets("Sheet2").AutoFilter.Range.Offset(1).EntireRow.Copy Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp)(2)
End Sub

 

[pwill]

Hi DanteAmor,
Thanks for your reply I've been working all day I will give it a try and let you know how I get on
Much appreciated

pwill

 

[DanteAmor]

Hi DanteAmor,
Thanks for your reply I've been working all day I will give it a try and let you know how I get on
Much appreciated

pwill

Ok, Let me know if you have any doubt.

 

[pwill]

My mistake, I thought it was the sheet1.Try this

Sub Filter_with_multiple_conditions()
    Dim c As Range, n As Long, arr() As Variant
    For Each c In Sheets("Sheet1").Range("R5", Sheets("Sheet1").Range("R" & Rows.Count).End(xlUp))
        ReDim Preserve arr(n)
        arr(n) = c.Text
        n = n + 1
    Next
    Sheets("Sheet2").UsedRange.AutoFilter Field:=1, Criteria1:=arr, Operator:=xlFilterValues
    Sheets("Sheet2").AutoFilter.Range.Offset(1).EntireRow.Copy Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp)(2)
End Sub

Works perfect, thank you DanteAmor

pwill
​

 

[DanteAmor]

Works perfect, thank you DanteAmor

pwill
​

I'm glad to help you. Thanks for the feedback.