VBA & Macro Problems

How to Use VBA to Refresh and Sort Multiple Excel Tables

Natalie Taylor Oct 1, 2026 869 views

Question details

The user needs to expand an existing VBA Worksheet_Change event to refresh and sort three additional Excel tables on the same worksheet.

How to Use VBA to Refresh and Sort Multiple Excel Tables

Product: Excel
Device & OS: not provided
Scenario: A worksheet change event is currently set up to refresh and sort a single table, and the user wants to apply this same behavior to four tables total without causing conflicts.
Observed behavior: The user needs to know how to structure the VBA code so that all four tables refresh and sort correctly using the same trigger event.

Before you start

Before modifying your VBA code, identify the exact ListObject names of the additional tables you wish to sort and ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm).

Solution 1Recommended

Consolidate Logic within a Single Worksheet_Change Event

Combine the sorting and refreshing logic for all tables into one event by declaring unique ListObject and Range variables for each table.

In Excel, a worksheet can only have one Worksheet_Change event. Attempting to create a separate Worksheet_Change sub-routine for each table will result in an 'Ambiguous name detected' error.

To sort multiple tables when a change occurs, you must place all the logic inside the single existing event. The key to preventing the code from breaking is to declare entirely separate variables for each new table.

Open the VBA Editor

Press ALT + F11 to open the Visual Basic for Applications editor, and double-click the specific worksheet module containing your existing Worksheet_Change event.

Declare Unique Variables

At the beginning of the event, declare separate ListObject and Range variables for the additional tables. For example: Dim SalesTable2 As ListObject, Dim SortCol2 As Range.

Set Variables for Each Table

Assign the new variables to their respective tables and sorting columns using the Set command. For example: Set SalesTable2 = Me.ListObjects("YourSecondTableName").

Create Separate With Blocks

Write distinct With blocks for each table's sort operation. Ensure you pair the correct variables together, using SalesTable2.Sort with SortCol2, SalesTable3.Sort with SortCol3, etc.

Consolidate Logic within a Single Worksheet_Change Event

Check Variable Names: If you copy and paste the sorting block for the new tables, ensure you update the variable names inside the With block. Failing to change 'SalesTable' to 'SalesTable2' will cause the macro to sort the first table repeatedly.

Run VBA Macros Seamlessly in WPS Spreadsheet

WPS Spreadsheet provides robust support for VBA and macros. You can easily manage, edit, and trigger Worksheet_Change events to sort multiple tables while enjoying a lightweight and highly compatible spreadsheet environment.

1. Install WPS Office: Download and install WPS Office, ensuring you select the version that includes the VBA module for macro support.
2. Open Your Macro Workbook: Launch WPS Spreadsheet and open your .xlsm file containing the tables you want to sort.
3. Access the VBA Editor: Navigate to the 'Developer' tab on the ribbon and click on 'Visual Basic' to open the code editor.
4. Edit and Run Your Code: Paste or modify your combined Worksheet_Change logic in the sheet module, save your progress, and trigger the event by modifying a cell.

Fully compatible with Microsoft Excel (.xlsm) formatsBuilt-in VBA editor for writing and debugging macrosLightweight application with high performance for heavy data processingFamiliar user interface requiring zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Can I have multiple Worksheet_Change events on the same sheet?

No, Excel and WPS Spreadsheet only allow one Worksheet_Change event per worksheet. You must combine the logic for all conditions, tables, and actions into that single event block.

Why does only my first Excel table refresh and sort?

This usually happens if you copied and pasted the sorting logic but forgot to update the variable names (like ListObject and Range) for the subsequent tables. Ensure each table block references its uniquely declared variables.

How do I find the correct name of my Excel table for VBA?

Click anywhere inside your table, navigate to the 'Table Design' tab on the ribbon, and check the 'Table Name' box on the far left. Use this exact name in your VBA ListObjects("TableName") reference.

Excel & Data Problems