VBA & Macro Problems

How to Update Shape Cell References After Copying Shapes in Excel

Tauseeq MagsiTauseeq Magsi Nov 11, 2026 868 views

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.

How to Update Shape Cell References After Copying Shapes in Excel
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 you start

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.

Solution 1Recommended

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.

1
Open the VBA Editor

Press the 'Alt + F11' keys simultaneously on your keyboard to open the Visual Basic for Applications (VBA) editor.

2
Insert a New Module

Click on 'Insert' in the top menu bar, then select 'Module' to create a blank canvas for your script.

3
Write the Reference Update 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).

4
Modify the Formula String

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').

5
Run the Macro

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.

Use a VBA Macro to Offset Linked Shape References
Reference Type Variation: The specific VBA syntax needed will vary depending on whether the shape's reference is stored as a linked cell formula, an assigned macro, or plain text within the shape's text frame.
Efficient Spreadsheet Management

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. 1. Open Your Workbook in WPS Spreadsheet: Launch WPS Office and open your existing spreadsheet containing the shapes and data.
  2. 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. 3. Access the VBA Editor: Click on 'VBA Editor' to open the built-in scripting environment and paste your shape-reference updating code.
  4. 4. Execute the Macro: Run your VBA script to automatically scan the spreadsheet and update the cell references for all your duplicated shapes.
Full compatibility with Microsoft Excel formats (.xlsx, .xlsm).Built-in VBA environment for creating, editing, and running complex macros.Advanced shape formatting and absolute/relative cell linking capabilities.Lightweight design that runs incredibly fast on Windows, Mac, and Linux.
microsoft office alternative - wps office

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.