VBA & Macro Problems

Create an Excel VBA Button to Filter Data into a Results Table

Chanuka GeekiyanageChanuka Geekiyanage Nov 18, 2026 870 views

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.

Create an Excel VBA Button to Filter Data into 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.
Before you start

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.

Solution 1Recommended

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.

1
Set up the tables

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.

2
Add the macro

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

3
Insert and assign the button

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.

4
Test the results

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.

Assign a VBA matching macro to an Excel button
Matching and output limits: This example performs exact text comparisons apart from letter case; it does not use partial matches or multiple criteria columns. No matches leave the results table without data rows. If an error occurs during the refresh, the table may contain partial results.
Work with spreadsheet macros in WPS

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. 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. 2. Prepare the workbook: Open the XLSM workbook and set up the CriteriaTable, SourceTable, and ResultsTable names and headers as described above.
  3. 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. 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.
Refresh a results table from matching criteria using a VBA macro where supported.Open and work with Microsoft Excel formats, including XLSX and XLSM.Use a familiar spreadsheet interface for managing tables and data.Keep the criteria, source data, and results on separate sheets.
microsoft office alternative - wps office

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.