VBA Filter Data with Multiple Values and Copy to Another Sheet

With a short macro you can filter data with multiple values in VBA and copy the result to another sheet in one click. Instead of ticking names in the filter drop-down every day, you type the values you want in a list, press a button and the macro filters the data, copies the matching rows to an Output sheet and clears the filter again. In this tutorial you will build that macro step by step: first with a fixed Array of names, then with a dynamic array that reads the criteria straight from a worksheet range. It works in Excel 2010 and every later version for Windows.

⬇️  Download the Filter Macro Workbook
3 sheets (Data, Filter_Criteria, Output) · .xlsm (macro-enabled) · Excel 2010 and later

Watch the Video Tutorial

In the video PK writes the macro from scratch, tests it with a fixed list of three employees, turns it into a dynamic list read from the Filter_Criteria sheet and assigns it to a Copy Data button.

What You Will Build

• A criteria list on its own sheet where you type any number of employee names.
• A Copy Data button that runs the macro with one click.
• An AutoFilter with multiple values on the EMP Name column, using xlFilterValues.
• Automatic copy of the visible rows to the Output sheet, which is cleared before every run.
• A clean Data sheet: the filter is removed again after the copy and a message confirms the result.

How the Workbook Is Laid Out

• Data – A1:D131 with the headers Date, EMP Name, Supervisor Name and Login Hours. It holds 130 rows: employees EMP-1 to EMP-10 for 1 to 13 January 2018.
• Filter_Criteria – the header EMP Name in A1 and the names to filter from A2 down. In the download file the list is EMP-1, EMP-5, EMP-3 and EMP-4. The Copy Data button sits next to the list.
• Output – receives the result and is cleared at the start of every run. With the sample list it holds the header plus 52 rows (4 employees x 13 days).

Part 1 – Filter with a Fixed List of Values

Step 1: Insert a module

Press Alt+F11 (or Developer → Visual Basic) to open the Visual Basic Editor, then choose Insert → Module. Start a new macro called Filter_My_Data.

Step 2: Declare and set the three sheets

Give each sheet a variable, so the rest of the code is short and easy to read:

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")

Step 3: Clear the output and any old filter

Output_sh.UsedRange.Clear
Data_sh.AutoFilterMode = False

The first line wipes everything on the Output sheet, so old results never mix with new ones. The second line removes any filter that is already on the Data sheet, so the macro always starts from the full data.

Step 4: Apply the filter with Array

To test the idea, PK first types three names directly into the code:

Data_sh.UsedRange.AutoFilter 2, Array("EMP-1", "EMP-2", "EMP-3"), xlFilterValues

• 2 is the field number: the second column of the range, which is EMP Name (column B).
• Array(...) holds the values to keep. You can list as many as you need.
• xlFilterValues tells Excel that the criteria is a list of values. It is the same as ticking several items in the filter drop-down.

Step 5: Copy the visible rows and finish

Data_sh.UsedRange.Copy Output_sh.Range("A1")
Data_sh.AutoFilterMode = False
MsgBox ("Data has been Copied")

When a range is filtered, Copy takes only the visible rows, so the Output sheet receives the header plus the matching records. Then the filter is removed and a message confirms the copy. Run it with F5 and the Output sheet shows the rows for EMP-1, EMP-2 and EMP-3.

Part 2 – Read the Values from a Worksheet (Dynamic Array)

A fixed Array is fine for a test, but you do not want to edit the code every time the list changes. The next step reads the names from the Filter_Criteria sheet into an array. The difficulty is that you do not know how long the list will be, so the array size has to be calculated first.

Step 6: Count the names and size the array

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 Emp_list() As String declares an array with no size yet, a dynamic array.
• CountA counts the non-blank cells in column A. With a header and three names it returns 4.
• The code subtracts 2: one for the header, and one because a VBA array starts at index 0. Three names need the positions 0, 1 and 2, so the upper bound is 4 – 2 = 2.
• ReDim Emp_list(n) gives the array exactly that size.

Step 7: Fill the array with a loop

Dim i As Integer

For i = 0 To n
    Emp_list(i) = Filter_Criteria_Sh.Range("A" & i + 2)
Next i

When i is 0 the loop reads A2, when it is 1 it reads A3, and so on. Adding 2 to i skips the header row and lines the index up with the first name.

Step 8: Use the array in the filter

Replace the fixed Array(...) with the new array:

Data_sh.UsedRange.AutoFilter 2, Emp_list(), xlFilterValues

The Complete VBA Code

This is the full macro from the download file:

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

Part 3 – Add the Copy Data Button

Step 9: Assign the macro to a shape

On the Filter_Criteria sheet insert a rectangle or rounded rectangle (Insert → Shapes), type Copy Data on it and format it as you like. Right-click the shape, choose Assign Macro and select Filter_My_Data. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm).

Step 10: Test with a new list

Add EMP-4 below the existing names and click Copy Data. The message Data has been Copied appears, and the Output sheet now holds the rows for EMP-1, EMP-3, EMP-4 and EMP-5. The rows stay in the same order as on the Data sheet, not in the order of your list.

Modern Excel Alternative: the FILTER Function (Addition)

This option is not in the original file. If you have Microsoft 365 or Excel 2021 and you only need a live view rather than a pasted copy, one formula in A2 of an empty sheet does the same job:

=FILTER(Data!A2:D131,ISNUMBER(MATCH(Data!B2:B131,Filter_Criteria!A2:A20,0)))

• MATCH(Data!B2:B131,Filter_Criteria!A2:A20,0) looks up every employee name in the criteria list and returns a position or #N/A.
• ISNUMBER(...) turns that into TRUE for names in the list and FALSE for the rest.
• FILTER returns only the rows marked TRUE, and the result updates as soon as you change the list.

Format the first column as a date. The macro is still the better choice when you need a static copy, older Excel versions or a one-click process for other users.

Common Errors and Fixes

Run-time error 9: Subscript out of range

Either a sheet name in the Set lines does not match the tab name exactly, or n became negative. In the video the first run used Count instead of CountA; Count only counts numbers, so it returned 0 and n became -2. Use CountA, because the names are text.

Some names are ignored

The loop reads exactly as many cells as CountA finds, starting from A2. If the list has a blank row in the middle, or other text sits in column A of the criteria sheet, the count no longer matches the list. Keep the names in one unbroken block from A2 and keep column A free of anything else.

The Output sheet has only the header

None of the values matched. Check the spelling and look for extra spaces, for example EMP-1 with a trailing space. Numbers and dates can also fail, because xlFilterValues compares the values as they are displayed in the cells.

Extra cells appear on the Output sheet

UsedRange includes everything that was ever used on the Data sheet, such as notes or links beside the table. Keep the Data sheet for the table only, or replace Data_sh.UsedRange with Data_sh.Range("A1").CurrentRegion in both the AutoFilter and Copy lines.

Tips

• Change the field number 2 to filter a different column, for example 3 for Supervisor Name.
• Rename the button and the criteria header so other users understand what to type.
• Turn on Application.ScreenUpdating = False at the start (and back to True at the end) to make the macro run without flicker on large data.
• Read Microsoft’s Range.AutoFilter method reference for all its arguments and operators.

Want It Ready-Made?

If you would rather use finished macro tools than write each one yourself, the Excel VBA Tools & Utilities Pack on NextGenTemplates.com bundles 8 working Excel VBA applications with data entry forms, reports and editable VBA source. You can also browse all Excel VBA tools.

Related Tutorials

• VBA UserForm to Segregate Data into Multiple Worksheets
• Split Data into Separate Workbooks in Excel with VBA
• Data Consolidation with VBA: Let the User Select Files at Runtime
• FILTER Function in Excel with Examples
• Learn Excel VBA Step by Step

Frequently Asked Questions

How do I filter by multiple values in Excel VBA?

Use the AutoFilter method with an array of values and the operator xlFilterValues. Step 4 shows the version with a fixed Array and Step 8 shows the version that reads the values from a worksheet list.

How do I use a range of cells as filter criteria in VBA?

Read the cells into a dynamic array first. Count the names with CountA, size the array with ReDim and fill it in a loop, then pass the array to AutoFilter as shown in Part 2.

Why does the code subtract 2 from CountA?

One is for the header in A1, and one is because VBA arrays start at index 0. Three names need the positions 0 to 2, so the upper bound is the count of 4 minus 2.

Does the macro copy hidden rows too?

No. When a range is filtered with AutoFilter, the Copy method copies only the visible rows, so the Output sheet receives the header and the matching records.

Can I do this without VBA?

Yes, in Microsoft 365 or Excel 2021. The FILTER function combined with ISNUMBER and MATCH returns the rows for every name in the criteria list, as shown in the Modern Excel Alternative section.

About the Author

PK is a Microsoft Excel expert and trainer and the founder of PK: An Excel Expert and NextGenTemplates.com. He has been teaching Excel, VBA, Power Query and Power BI since 2016 on his YouTube channel.

Conclusion

To filter data with multiple values in VBA, you only need AutoFilter with xlFilterValues and an array of criteria. Read that array from a worksheet list with CountA, ReDim and a short loop, copy the visible rows to the Output sheet and put the macro behind a button. Your users then type the names, click Copy Data and get a clean report every time. Download the file and try it with your own list.