Create an Excel VBA Button to Filter Data into a Results Table
Question details
Create a button that uses VBA to match criteria from one table against a source table and writes matching records to a results table.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A workbook contains a criteria table, a source table on another sheet, and a results table that should be refreshed when a button is clicked.
- Observed behavior
- The results table should contain matching source rows and remain empty when no records match.
In desktop Excel, create three Excel tables and note their sheet names, table names, and column headers. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm), and enable macros only if you trust the workbook.
Assign a VBA matching macro to an Excel button
Use this approach to match one criteria column against one source column and refresh a separate results table.
The example matches nonblank Key values without regard to letter case. Each source row is added at most once, even if a criterion appears more than once. It copies values, not formulas.
The results table must have the same headers in the same order as the source table. Structured table references allow the macro to include rows added later.
Create a table named CriteriaTable on a sheet named Criteria, with a column named Key. Create SourceTable on a sheet named Data, also with a Key column. Create ResultsTable on a sheet named Results, with the same headers and column order as SourceTable. Use Table Design > Table Name to check each table name.
Open Developer > Visual Basic, choose Insert > Module, and paste this code: Sub RefreshResults() Dim criteriaTable As ListObject, sourceTable As ListObject, resultsTable As ListObject Dim sourceRow As ListRow, resultRow As ListRow, criterion As Range Dim keyColumn As Long, i As Long Dim sourceValue As Variant, criterionValue As Variant On Error GoTo HandleError Set criteriaTable = ThisWorkbook.Worksheets("Criteria").ListObjects("CriteriaTable") Set sourceTable = ThisWorkbook.Worksheets("Data").ListObjects("SourceTable") Set resultsTable = ThisWorkbook.Worksheets("Results").ListObjects("ResultsTable") keyColumn = sourceTable.ListColumns("Key").Index If sourceTable.ListColumns.Count <> resultsTable.ListColumns.Count Then MsgBox "Source and results tables must have the same columns." Exit Sub End If For i = 1 To sourceTable.ListColumns.Count If sourceTable.ListColumns(i).Name <> resultsTable.ListColumns(i).Name Then MsgBox "Source and results headers must match in the same order." Exit Sub End If Next i ' Check that the criteria column exists before clearing results. i = criteriaTable.ListColumns("Key").Index Do While resultsTable.ListRows.Count > 0 resultsTable.ListRows(1).Delete Loop If criteriaTable.DataBodyRange Is Nothing Or sourceTable.DataBodyRange Is Nothing Then Exit Sub For Each sourceRow In sourceTable.ListRows sourceValue = sourceRow.Range.Cells(1, keyColumn).Value If Not IsError(sourceValue) Then If Len(Trim$(CStr(sourceValue))) > 0 Then For Each criterion In criteriaTable.ListColumns("Key").DataBodyRange.Cells criterionValue = criterion.Value If Not IsError(criterionValue) Then If Len(Trim$(CStr(criterionValue))) > 0 Then If StrComp(CStr(sourceValue), CStr(criterionValue), vbTextCompare) = 0 Then Set resultRow = resultsTable.ListRows.Add resultRow.Range.Value = sourceRow.Range.Value Exit For End If End If End If Next criterion End If End If Next sourceRow Exit Sub HandleError: MsgBox "Could not refresh results: " & Err.Description End Sub
In Excel, choose Developer > Insert > Button (Form Control), draw the button on a sheet, and select RefreshResults in the Assign Macro dialog. If the Developer tab is hidden, enable it through Excel's ribbon customization settings.
Enter one or more values in CriteriaTable's Key column and click the button. Check that matching SourceTable rows appear in ResultsTable; change the criteria and click again to replace the previous results.

Build a criteria-based results table in WPS Spreadsheets
In a WPS Office installation that supports VBA and form controls, you can open the workbook, add the matching macro, and assign it to a button. VBA availability and setup vary by edition, so check your installation before relying on the button.
- 1. Check VBA support: Open WPS Spreadsheets and check the Developer tools for a VBA editor and button or form-control options. If they are unavailable, check the VBA requirements for your WPS edition.
- 2. Prepare the workbook: Open the XLSM workbook and set up the CriteriaTable, SourceTable, and ResultsTable names and headers as described above.
- 3. Add and assign the macro: Use the Developer tools to open the VBA editor, insert a module, and paste RefreshResults. Insert a supported button control and assign the macro.
- 4. Run a test: Save the workbook as XLSM, enter criteria in the Key column, and click the button to check the results. Enable macros only for a trusted workbook.

Frequently Asked Questions
What happens if none of the criteria match?
The macro deletes the existing results first. If no source rows match, ResultsTable remains without data rows.
Will the macro find rows added to the source table later?
Yes. Add the rows within SourceTable, then click the button again. The macro loops through the table's current rows.
Why does the macro report that a table or column cannot be found?
Check the sheet names and use Table Design > Table Name to verify CriteriaTable, SourceTable, and ResultsTable. Also confirm that both the criteria and source tables have a Key column.
Does repeating a criterion duplicate matching results?
No. The macro stops checking criteria after the first match for each source row, so that row is copied only once.
Can I use this macro for partial matches?
Not as written. It compares complete cell values, ignoring letter case. Partial matching requires changing the comparison in the VBA code.




