VBA & Macro Problems

How to Synchronize Matching Cells Between Excel Worksheets Using VBA

Steve K Sep 28, 2026 868 views

Question details

The user needs a method to mutually edit and synchronize corresponding cells across two or more worksheets without causing a circular reference error.

How to Synchronize Matching Cells Between Excel Worksheets Using VBA

Product: Microsoft Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Updating a cell value on one worksheet and needing that exact change to reflect automatically on the corresponding cells in other sheets, and vice versa.
Observed behavior: Standard worksheet formulas create a circular reference error when trying to make cells mutually dependent. A VBA-based solution is required to copy the changes seamlessly.

Before you start

Ensure that your workbook is saved as a Macro-Enabled Workbook (.xlsm) so that your VBA scripts are retained, and back up your data before running new macros.

Solution 1Recommended

Use a Workbook_SheetChange VBA Event

Implement a macro in the ThisWorkbook module to detect cell changes and synchronize them across specified sheets while temporarily disabling events to prevent infinite loops.

Because standard formulas only pull data in one direction, trying to link cells mutually will trigger a circular reference warning. A VBA event macro solves this by actively pushing the data to the other sheet only when a manual change occurs.

Open the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.

Access the ThisWorkbook Module

In the Project Explorer pane on the left, locate your workbook name, expand the Microsoft Excel Objects folder, and double-click 'ThisWorkbook'.

Insert the Event Code

Select 'Workbook' from the left dropdown menu and 'SheetChange' from the right dropdown menu. Input your macro code to detect changes in your desired range (e.g., Column A) and copy the target value to the corresponding sheet.

Disable Events to Prevent Loops

Inside your code, wrap your cell-copying action with 'Application.EnableEvents = False' before the copy, and 'Application.EnableEvents = True' immediately after. This prevents the macro from triggering itself continuously.

Save and Test

Close the VBA editor and save your file as an Excel Macro-Enabled Workbook. Test the synchronization by typing a value into the specified range on one of the synced sheets.

Synchronizing Multiple Sheets: You can expand this synchronization to more than two worksheets by adding their names to an array within your VBA code, such as Array("Sheet1", "Sheet2", "Sheet3").

Advanced Spreadsheet Capabilities

Run Your VBA Macros Seamlessly with WPS Spreadsheet

WPS Office includes robust, built-in support for VBA macros, allowing you to synchronize worksheets, automate repetitive tasks, and edit scripts easily. It serves as a highly compatible, lightweight alternative to Microsoft Excel.

1. Download and Install WPS Office: Download the free WPS Office suite from the official website and complete the installation.
2. Open Your Macro Workbook: Launch WPS Spreadsheet and open your existing .xlsm file containing the data you want to synchronize.
3. Navigate to the Developer Tab: Click on the 'Developer' tab located on the top ribbon menu to access advanced tools.
4. Access the VBA Editor: Click the 'Visual Basic' icon to open the integrated VBA editor.
5. Add or Edit Your Script: Double-click 'ThisWorkbook', paste your Workbook_SheetChange synchronization code, save the file, and return to your spreadsheet to test it.

Full native support for Excel VBA macros and event scriptsSeamless compatibility with Microsoft Excel .xlsm and .xlsb formatsLightweight application with a familiar ribbon interfaceFree to use for everyday document and spreadsheet tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get a circular reference error when using standard formulas to sync cells?

Formulas are designed to pull data in a single direction. If Cell A relies on Cell B, and Cell B relies back on Cell A, it creates an infinite calculation loop known as a circular reference, which Excel actively prevents to avoid crashing.

What does Application.EnableEvents do in this VBA script?

Setting Application.EnableEvents to False temporarily pauses Excel from listening for changes. When your macro updates the second sheet, this pause prevents the update from triggering the macro again, successfully avoiding an endless loop.

Can I synchronize only a specific column instead of the entire worksheet?

Yes. Within your VBA code, you can use the Intersect function to verify if the Target (the changed cell) falls within a specific range, such as Range("A:A"). The macro will only execute the synchronization if the changed cell is inside that designated column.

Do I need to rewrite my Excel VBA code to use it in WPS Office?

No, WPS Office provides excellent compatibility with Microsoft Excel VBA syntax. Standard event handlers like Workbook_SheetChange will execute properly in WPS Spreadsheet without requiring any code modifications.

Excel & Data Problems