VBA & Macro Problems

How to Lookup Between Worksheets and Copy Hyperlinks using VBA

Chanuka Geekiyanage Sep 28, 2026 869 views

Question details

The user needs to match values between different worksheets, copy data from one column to another while preserving hyperlinks, and ensure that destination cells remain blank when the source cells are empty.

How to Lookup Values Between Worksheets and Copy Hyperlinks in Excel

Product: Excel
Device & OS: not provided
Scenario: Looking up and migrating data across worksheets using VBA or formulas, with a specific requirement to retain clickable hyperlink formatting and avoid zero values for blanks.
Observed behavior: Standard lookups and value-only VBA scripts return a zero when referencing an empty cell and only copy the plain text value, stripping out the active hyperlink.

Before you start

Ensure you have the Developer tab enabled to access the VBA Editor, and remember to save a backup copy of your workbook before running new macro scripts.

Solution 1Recommended

Use a VBA Macro to Lookup and Copy Cell Objects

Using VBA to copy the actual cell object rather than just its value ensures that hyperlinks are preserved in the destination column.

While formulas pull cell values, VBA can be used to copy entire cell properties. By looping through the lookup range and using the Copy method (or recreating the Hyperlinks collection entry), you can retain clickable links.

To prevent blank source cells from producing zeroes, you can add an IF condition within your VBA loop to verify if the source cell is empty before copying.

Open the VBA Editor

Press ALT + F11 on your keyboard to launch the Microsoft Visual Basic for Applications window.

Insert a New Module

Click on 'Insert' in the top menu and select 'Module'. This will create a blank canvas for your script.

Enter the Lookup and Copy Code

Write a VBA routine that finds the matching row in Worksheet 1. Instead of assigning '.Value = .Value', instruct the macro to use 'Range.Copy' from the source cell in column Y and 'PasteSpecial' to the destination in column J. Add an 'If Trim(SourceCell.Value) <> "" Then' check to skip blanks.

Run the Macro

Close the VBA editor, return to your worksheet, and press ALT + F8. Select your new macro and click 'Run' to execute the data transfer.

Use a VBA Macro to Lookup and Copy Cell Objects

Preserving Hyperlinks: By copying the cell itself rather than injecting a formula via VBA, all formatting, including active web links and internal document links, will remain intact.

Solution 2

Use XLOOKUP Formula to Handle Blanks

If you do not strictly need clickable hyperlinks for every cell and prefer a simpler formula approach, XLOOKUP can be combined with an IF statement to handle blanks.

Seamless VBA Support

Automate Data Lookups and Hyperlinks with WPS Spreadsheet

WPS Office offers robust support for VBA macros and advanced formulas like XLOOKUP. You can easily automate data transfers between worksheets while fully preserving formatting and hyperlinks, all in a lightweight interface.

1. Open Your Workbook: Launch WPS Spreadsheet and open your existing .xlsm or .xlsx file.
2. Access the Developer Tools: Navigate to the 'Developer' tab on the top ribbon and click 'VBA Editor'.
3. Insert Your Macro: Paste your custom lookup and hyperlink-copying VBA script into a new module.
4. Run and Automate: Execute the macro to instantly match values across worksheets while retaining all link formatting.

Fully compatible with Microsoft Excel formats (.xlsx, .xlsm)Built-in VBA/Macro support for powerful automationIncludes advanced functions like XLOOKUP, VLOOKUP, and HYPERLINKLightweight, fast, and completely free to download

microsoft office alternative - wps office

Frequently Asked Questions

Why does my lookup formula return a 0 for blank cells?

By default, Excel evaluates empty source cells as the number zero when referenced by a formula. You can avoid this by wrapping your lookup function in an IF statement, such as =IF(A2="","",XLOOKUP(...)), to force it to return a true blank.

Can VLOOKUP or XLOOKUP retrieve hyperlinks natively?

No, standard lookup formulas retrieve only cell values (text or numbers), not the cell's underlying formatting or hyperlink properties. To keep links clickable, you must use a VBA macro to copy the cell object or wrap your lookup formula inside the HYPERLINK function.

How do I run a VBA macro to copy data in my workbook?

Press ALT + F11 to open the VBA editor, click Insert > Module, and paste your code. Then, go back to your spreadsheet, press ALT + F8, select the macro name from the list, and click Run.

Excel & Data Problems