How to Copy Excel Hyperlinks Between Worksheet Tabs Dynamically
Question details
The user needs a method to duplicate an Excel worksheet containing internal hyperlinks so that the links on the copied tab point to target cells within the newly created sheet, rather than statically linking back to the original sheet.
- Product
- Spreadsheets
- Device & OS
- not provided
- Scenario
- Creating a reusable worksheet template where internal navigation links update automatically upon duplication.
- Observed behavior
- When a worksheet is copied, standard hyperlinks remain anchored to the original worksheet's name instead of updating to the new copied tab's name.
Ensure your workbook has been saved at least once to your local drive. The formula used to extract the current worksheet name requires a saved file path to function correctly.
Use the HYPERLINK and CELL Functions to Create Dynamic Links
Replace static hyperlinks with a dynamic HYPERLINK formula that detects the current sheet's name. This ensures that any copied sheet generates links pointing to its own tab.
Standard hyperlinks created via the 'Insert Link' menu use absolute references to a specific sheet name. To make links dynamic upon copying the sheet, you must use Excel's built-in formulas to extract the active sheet name automatically.
Select an empty cell (for example, A1) and input the formula: =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255). Press Enter. This cell will now display the name of the current worksheet tab.
In the cell where you want the clickable link to appear, enter the formula: =HYPERLINK("#'" & A1 & "'!B10", "Jump to Target Cell"). Replace 'B10' with your actual destination cell reference. This concatenates the sheet name from A1 with your target cell.
Right-click the worksheet tab at the bottom of the window, select 'Move or Copy', check the 'Create a copy' box, and click 'OK'. The formulas on the new sheet will automatically recalculate the new tab's name, and your hyperlinks will now point to the correct internal locations on the copied sheet.
Easily Manage Dynamic Hyperlinks with WPS Spreadsheet
WPS Spreadsheet fully supports advanced functions like HYPERLINK, CELL, and MID to help you build smart, reusable templates. You can effortlessly duplicate worksheets and keep your internal navigation fully functional.
- 1. Open your template workbook: Launch WPS Spreadsheet and open the document containing the worksheet you want to copy.
- 2. Apply dynamic formulas: Use the combination of HYPERLINK and CELL formulas to configure your internal navigation links.
- 3. Copy the worksheet: Right-click the sheet tab, choose 'Move or Copy', check 'Create a copy', and confirm. WPS Spreadsheet will instantly recalculate the links for the new tab.
Frequently Asked Questions
Why do standard inserted hyperlinks still point to the original sheet after copying?
Standard hyperlinks inserted via 'Right-click > Link' are absolute references anchored to the specific sheet name at the time of creation. To make them dynamic and update upon copying, you must use the HYPERLINK formula.
Why does my CELL("filename") formula return an error?
The workbook must be saved to your computer first. If it is a brand-new, unsaved file, the formula cannot determine the file path or sheet name and will return an error.
Can I use VBA to update hyperlinks after copying a sheet?
Yes, you can write a VBA macro that triggers on the Workbook_NewSheet event to iterate through all standard hyperlinks on the newly created sheet and replace the old sheet name in the SubAddress property with the active sheet's name.
