How to Link Cell Text and Hyperlinks Between Excel Worksheets
Question details
The user wants to link both the displayed text and its underlying clickable hyperlink from one worksheet to another.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Referencing a cell containing a hyperlink located in a different worksheet while maintaining its clickability in the destination sheet.
- Observed behavior
- Standard formula references (e.g., =Sheet1!A1) only transfer the display text, stripping the underlying hyperlink and rendering it unclickable.
Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) if you plan to use the VBA solution to copy embedded hyperlinks.
Use a VBA Macro to Transfer Text and Hyperlinks Automatically
Since standard formulas cannot extract embedded hyperlinks, a VBA macro is required to directly copy a cell's embedded hyperlink and display text to another worksheet.
A VBA script is the most reliable way to synchronize a master sheet's links with other worksheets. It accesses the actual hyperlink object properties that formulas cannot read.
Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) Editor.
In the top menu, click on 'Insert' and select 'Module' to create a blank workspace for your script.
Copy and paste a macro script that uses the `.Hyperlinks(1).Address` property to copy the link from your source range and apply it to your destination range using `ActiveSheet.Hyperlinks.Add`.
Press F5 or click the 'Run' button to execute the script and transfer the text along with the clickable hyperlinks.
Use the HYPERLINK Function with a Helper Column
Best for simple setups where you can store the actual URL and the display text in separate cells.
Seamlessly Run VBA Macros and Transfer Hyperlinks in WPS Office
WPS Spreadsheets provides robust support for VBA macros and advanced formulas, allowing you to easily link cell text and hyperlinks across worksheets just like you would in Microsoft Excel.
- 1. Open Your Workbook: Launch WPS Spreadsheets and open the workbook containing your hyperlinks.
- 2. Access the VBA Editor: Navigate to the 'Tools' tab and click on 'Macro', or simply press Alt + F11 to open the WPS VBA Editor.
- 3. Apply the VBA Script: Insert a new module, paste your hyperlink-transfer VBA code, and run it to instantly synchronize your links.
- 4. Save as Macro-Enabled File: Go to 'Menu' > 'Save As' and select 'Excel Macro-Enabled Workbook (*.xlsm)' to ensure your code is saved securely.
Frequently Asked Questions
Why doesn't a standard formula like =Sheet1!A1 keep the hyperlink?
Standard cell references in spreadsheet applications only retrieve the cell's 'value' (the displayed text). They do not inherit the source cell's formatting or embedded properties, such as background colors or embedded hyperlinks.
Can I extract a hyperlink URL using a native formula without VBA?
No, native formulas cannot extract an embedded hyperlink URL directly from a cell. To use formula-only solutions, the URL must be typed out as a plain text string in an adjacent helper cell so it can be referenced.
Do macros that transfer links update automatically?
Not by default. A standard macro must be run manually every time the source link changes. However, you can automate this by placing the VBA code inside a 'Worksheet_Change' event so it triggers automatically upon edits.
Does WPS Office support VBA for linking hyperlinks?
Yes, WPS Spreadsheets fully supports VBA macros. You can use the exact same VBA code you would use in Excel to copy cell text and hyperlinks across worksheets in WPS.
