VBA & Macro Problems

How to Fix Excel VBA Lookups Using Data from Another Worksheet

Maira Mehtab Oct 10, 2026 870 views

Question details

The user needs to update Excel VBA drop-down code to correctly reference lookup data located on a different worksheet and safely merge multiple Worksheet_Change events.

Fix Excel VBA Lookups Using Data from Another Worksheet

Product: Microsoft Excel
Device & OS: not provided
Scenario: Updating a VBA macro that relies on Worksheet_Change events to perform data lookups across multiple worksheets for varying target columns.
Observed behavior: The macro throws an error at 'End Sub', and lookups fail due to incorrect ActiveSheet references and duplicated Worksheet_Change event procedures for different columns.

Before you start

Before modifying your code, open the VBA Editor (Alt + F11) and verify the exact tab names of your source and destination worksheets, as incorrect string names in your code will trigger a 'Subscript out of range' error.

Solution 1Recommended

Consolidate Events and Use Explicit Worksheet References

Combine multiple change events into one unified macro and replace vague ActiveSheet references with explicit worksheet names to ensure data is pulled from the correct source.

Excel VBA only allows a single Worksheet_Change event per worksheet module. If you need to monitor multiple columns (like column 4 and column 6), you must place all conditional logic inside that single event.

Relying on ActiveSheet for lookups often causes errors when the macro attempts to read lookup arrays. Pointing explicitly to your data sheets prevents execution failures.

Remove Duplicate Event Procedures

Open the VBA Editor (Alt + F11). Navigate to the specific sheet module and ensure there is only one 'Private Sub Worksheet_Change(ByVal Target As Range)'. Delete or comment out any duplicate Worksheet_Change subroutines.

Assign Specific Target Columns

Inside the single Worksheet_Change event, use If and ElseIf statements to handle different columns. For example, use 'If Target.Column = 4 Then' for your first lookup block, and 'ElseIf Target.Column = 6 Then' for the second lookup block.

Add Explicit Worksheet References

Update your lookup code to explicitly call the correct data sheet. Replace any implicit references or 'ActiveSheet' with the explicit command, such as 'Worksheets("Materials")' or 'Worksheets("Time")'.

Disable and Enable Application Events

Wrap your code blocks with 'Application.EnableEvents = False' at the beginning and 'Application.EnableEvents = True' before the 'End Sub'. This prevents your lookup macro from triggering itself continuously when it writes new values.

Consolidate Events and Use Explicit Worksheet References

Best Practice: Always ensure 'Application.EnableEvents = True' is included in your error handler block so that events are safely re-enabled even if the macro crashes.

Run and Edit VBA Macros Seamlessly with WPS Spreadsheets

WPS Office provides comprehensive support for VBA macros, allowing you to easily write, edit, and execute complex Worksheet_Change events and cross-sheet lookups just as you would in Microsoft Excel.

1. Open your macro-enabled workbook: Launch WPS Spreadsheets and open your .xlsm file containing the lookup macros.
2. Access the VBA Editor: Navigate to the 'Developer' tab on the top ribbon and click on the 'Visual Basic' icon.
3. Edit Worksheet_Change Events: Double-click your target worksheet in the Project Explorer to edit and combine your Worksheet_Change code.
4. Test your lookups: Return to the spreadsheet interface and modify data in columns 4 and 6 to verify that your new explicit worksheet references run correctly.

Fully compatible with Microsoft Excel .xlsm and .xlsb formats.Built-in Visual Basic Editor for executing and adjusting cross-sheet lookup codes.Lightweight architecture ensures that heavy macros run smoothly without freezing.Completely free to use for basic spreadsheet processing.

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get an 'Ambiguous name detected: Worksheet_Change' error?

This error occurs because you have placed more than one Worksheet_Change event in the same sheet module. VBA restricts you to one event handler of a specific type per worksheet. You must merge all your column-specific logic into that single procedure.

How do I correctly reference a specific worksheet in VBA?

Instead of relying on the active sheet, explicitly declare the sheet name using Worksheets("YourSheetName"). For example, if your lookup table is on a sheet named Materials, use Worksheets("Materials").Range("A1:B10") to ensure the macro pulls data from the exact source table.

Why does my VBA macro freeze Excel when updating cells?

If your Worksheet_Change macro alters cell values, it registers as a new change, triggering the macro again and creating an infinite loop. To resolve this, add Application.EnableEvents = False before your update code, and Application.EnableEvents = True immediately after.

Excel & Data Problems