VBA & Macro Problems

How to Automatically Copy Excel Cells with Formatting and Hyperlinks

Maira Mehtab Sep 22, 2026 871 views

Question details

The user needs to synchronize specific cells across different worksheets, ensuring that both the cell text, visual formatting, and hyperlink URLs are copied automatically.

Product: Spreadsheets
Device & OS: not provided
Scenario: Maintaining identical data, including functional hyperlinks and styling, in specific cells across multiple worksheets without manually copying and pasting.
Observed behavior: Standard direct formulas (like =Sheet1!A1) only pull the text value into the new cell, dropping the underlying hyperlink address and the original cell formatting.

Before you start

Before applying VBA solutions, ensure you save your workbook as a Macro-Enabled Workbook (.xlsm) to prevent your code from being discarded upon closing, and clearly identify the exact source and target cell ranges you wish to sync.

Solution 1Recommended

Use a VBA Worksheet_Change Event to Sync Specific Cells

Since standard formulas cannot pull hyperlink URLs or formatting, using a VBA event is the most effective way to automatically synchronize specific cells across worksheets.

Standard Excel formulas evaluate and return values, which is why a simple reference drops formatting and hyperlink addresses. By using the Worksheet_Change event in the source sheet's module, you can program the spreadsheet to duplicate the exact cell properties—including the hyperlink—whenever a designated cell is modified.

Open the VBA Editor

Right-click the sheet tab containing your source data (e.g., Sheet1) at the bottom of the window and select 'View Code' to open the Visual Basic editor.

Set up the Worksheet_Change Event

In the code window, select 'Worksheet' from the top-left dropdown menu and 'Change' from the top-right dropdown menu to create the event structure.

Define the target range

Write an 'If Not Intersect(Target, Range("A1")) Is Nothing Then' statement to ensure the macro only triggers when your specific cell is modified.

Copy formatting and recreate the hyperlink

Add code within the IF block to copy the Target cell's value to the destination sheet, use the '.Hyperlinks.Delete' method to remove old links on the destination, and recreate the hyperlink using 'Target.Hyperlinks(1).Address'.

Save and test

Close the VBA editor and test the synchronization by updating the specified cell on your source sheet to see the formatting and hyperlink copy over automatically.

Macro Placement: The code must be placed directly in the specific worksheet module (e.g., Sheet1) where the edits will occur, rather than in a standard module, so that the event triggers automatically.

Solution 2

Group Worksheets to Mirror Entire Sheets

If you decide to duplicate formatting, hyperlinks, and text across entire worksheets simultaneously rather than specific cells, grouping is a fast, built-in alternative.

Advanced Spreadsheets Tool

Easily Manage Macros and Hyperlinks with WPS Spreadsheets

WPS Spreadsheets provides robust support for advanced data synchronization, including worksheet grouping and VBA macros. It offers a familiar interface, making it simple to manage complex workbook structures and linked cells.

1. Open your workbook: Launch WPS Spreadsheets and open the file where you need to synchronize hyperlink cells.
2. Access Developer Tools: Navigate to the 'Developer' tab on the top ribbon and click on 'VBA Editor'.
3. Insert your macro: Double-click the source worksheet in the Project Explorer on the left to insert your Worksheet_Change synchronization macro.
4. Save as Macro-Enabled: Go to File > Save As, and choose 'Excel Macro-Enabled Workbook (*.xlsm)' to keep your automated sync active.

Seamless compatibility with Microsoft Excel (.xlsx and .xlsm) formats.Built-in Developer tools for creating and managing VBA macros to copy formatting and links.Lightweight software that processes complex spreadsheet operations quickly.Free alternative with a highly familiar user interface that reduces the learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't a standard Excel formula copy the hyperlink?

Standard formulas only return the evaluated values of a cell, not the cell's underlying metadata. When you use a reference formula like =A1, it retrieves the display text but ignores the hyperlink address and visual formatting.

Will the VBA synchronization solution slow down my workbook?

If applied to a small, specific range using the Intersect function, the Worksheet_Change event runs almost instantaneously. However, if the code lacks range restrictions and triggers upon every single cell edit on the sheet, it can cause performance issues.

Do I need to manually update the target sheets if the hyperlink address changes?

No. As long as the VBA macro is set to monitor the cell containing the link, updating the URL on the source sheet will automatically trigger the macro to update the hyperlink addresses on the target sheets.

Can I copy hyperlinks using Conditional Formatting?

No, Conditional Formatting only applies visual styles (like colors and bold text) based on cell values. It cannot copy, generate, or transfer functional hyperlink URLs between cells.

Excel & Data Problems