Filter Sheet2 Data based on Sheet1 List Values

pwill

Active Member
Joined
Nov 22, 2015
Messages
406
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

ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
1RefCntRefCntRefCntRefCnt
212118131132
312118131132
412118131132
52627214216
62627214216
72627214216
835324330333
935324330333
1035324330333
114245410424
124245410424
134245410424
14518521522533
15518521522533
16518521522533
1769621629632
1869621629632
1969621629632
20720725727731
21720725727731
22720725727731
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

ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
1RefCntRefCntRefCntRefCnt
212118131132
312118131132
412118131132
52627214216
62627214216
72627214216
84245410424
94245410424
104245410424
1169621629632
1269621629632
1369621629632
14720725727731
15720725727731
16720725727731
17
18
19
20
21
22
23
24
25

<tbody>
</tbody>

Any help would be appreciated

regards

pwill
<strike>
</strike>
 
Last edited:

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,216
Office Version
2007
Platform
Windows
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

ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
1RefCntRefCntRefCntRefCnt
212118131132
312118131132
412118131132
52627214216
62627214216
72627214216
84245410424
94245410424
104245410424
1169621629632
1269621629632
1369621629632
14720725727731
15720725727731
16720725727731
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

Active Member
Joined
Nov 22, 2015
Messages
406
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
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,216
Office Version
2007
Platform
Windows
:oops: My mistake, I thought it was the sheet1.Try this


Code:
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

Active Member
Joined
Nov 22, 2015
Messages
406
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
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,216
Office Version
2007
Platform
Windows
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

Active Member
Joined
Nov 22, 2015
Messages
406
:oops: My mistake, I thought it was the sheet1.Try this


Code:
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,102,761
Messages
5,488,687
Members
407,651
Latest member
Halosty

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top