In this article you will learn how we can use an excel range as a filter criteria and paste the filtered data on another worksheet.
For example, we have below data in “Data” worksheet.

We have below given Employee list whose data has to be filtered and copied on “Output” worksheet.
Here we have created a macro to get the desired output.
Below is the VBA code:
- Copy the below given code and go to Visual Basic Editor (Press Alt+F11)
- Insert a module (Press Alt+I+M)
- Paste this code in the module.
- Save as the workbook as Macro enable workbook.
- Insert a rectangle shape in “Filter_Criteria” worksheet and right click on this shape and assign “Filter_My_Data()” macro.
Option Explicit Sub Filter_My_Data() Dim Data_sh As Worksheet Dim Filter_Criteria_Sh As Worksheet Dim Output_sh As Worksheet Set Data_sh = ThisWorkbook.Sheets("Data") Set Filter_Criteria_Sh = ThisWorkbook.Sheets("Filter_Criteria") Set Output_sh = ThisWorkbook.Sheets("Output") Output_sh.UsedRange.Clear Data_sh.AutoFilterMode = False Dim Emp_list() As String Dim n As Integer n = Application.WorksheetFunction.CountA(Filter_Criteria_Sh.Range("A:A")) - 2 ReDim Emp_list(n) As String Dim i As Integer For i = 0 To n Emp_list(i) = Filter_Criteria_Sh.Range("A" & i + 2) Next i Data_sh.UsedRange.AutoFilter 2, Emp_list(), xlFilterValues Data_sh.UsedRange.Copy Output_sh.Range("A1") Data_sh.AutoFilterMode = False MsgBox ("Data has been Copied") End Sub
Click here to download Excel template.
Watch the step by step video tutorial: