How to Update Shape Cell References After Copying Shapes in Excel
Question details
The user needs to update the cell references of shapes duplicated alongside a copied range so they accurately reflect the new pasted location instead of the original source range.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Copying a formatted area containing shapes with cell references and pasting it to a new location, requiring the duplicated shapes to point to the newly pasted range dynamically.
- Observed behavior
- Copied shapes retain their absolute references to the original source cells rather than automatically updating relative to their new pasted position.
Before running any VBA scripts to modify shape references en masse, ensure you have enabled the Developer tab in your spreadsheet and saved a backup copy of your workbook to prevent data loss.
Use a VBA Macro to Offset Linked Shape References
Using a VBA script is the most efficient method to automatically identify newly copied shapes based on their position and update their cell references to the new range.
When you copy shapes over to a new range, the software statically links them to the original cells by design. Utilizing a VBA macro allows you to loop through all shapes on the worksheet, check their spatial location via the TopLeftCell property, and update the linked formula dynamically.
Press the 'Alt + F11' keys simultaneously on your keyboard to open the Visual Basic for Applications (VBA) editor.
Click on 'Insert' in the top menu bar, then select 'Module' to create a blank canvas for your script.
Write a macro that loops through each shape on the active sheet. Use the 'TopLeftCell' property to check if the shape falls within your new pasted range (e.g., Column G).
Within the loop, capture the shape's formula (e.g., 'Shape.OLEFormat.Object.Formula') and use the VBA 'Replace' function to swap the original range address (like 'A1:E33') with the new target address (like 'G1:G33').
Close the VBA editor and press 'Alt + F8' to open the Macro dialog. Select your newly created macro and click 'Run' to update all shape references simultaneously.

Manually Update Linked Cells for Individual Shapes
If you only have a few shapes to manage, manually updating the formula bar for each copied shape is quick and avoids the need to write custom VBA code.
Manage Macros and Shapes Seamlessly with WPS Spreadsheet
WPS Office provides a powerful, free alternative for spreadsheet management, featuring full VBA macro support and advanced shape handling to automate tasks just like Microsoft Excel.
- 1. Open Your Workbook in WPS Spreadsheet: Launch WPS Office and open your existing spreadsheet containing the shapes and data.
- 2. Enable the Developer Tools: Navigate to the 'Developer' tab on the top ribbon. If it is not visible, you can enable it through the Options menu.
- 3. Access the VBA Editor: Click on 'VBA Editor' to open the built-in scripting environment and paste your shape-reference updating code.
- 4. Execute the Macro: Run your VBA script to automatically scan the spreadsheet and update the cell references for all your duplicated shapes.

Frequently Asked Questions
Why do shapes keep their original cell references when copied?
By default, spreadsheet applications treat shape formulas and linked cells as absolute references. When a shape is copied and pasted, it continues to point to the exact original cell address rather than shifting relatively like standard cell formulas.
Can I make shape cell references relative without using VBA?
No, shape references linked via the formula bar are strictly absolute. You cannot use relative referencing (like A1 instead of $A$1) directly for shape links, which is why a VBA macro is required to perform bulk offset updates.
How do I find the location of a pasted shape using VBA?
In VBA, you can determine a shape's position by calling the 'Shape.TopLeftCell' property. This returns a Range object representing the exact cell positioned immediately underneath the top-left corner of the shape.
Will the macro automatically update text typed directly inside the shape?
Not automatically. If the text inside the shape is manually typed rather than linked via a formula, your VBA macro must be specifically written to target the 'Shape.TextFrame.Characters.Text' property to find and replace those text strings.




