VBA & Macro Problems

How to Preserve Relative Formulas When Copying Ranges in Excel VBA

Khadija Khan Sep 27, 2026 870 views

Question details

The user needs to copy named ranges with relative formulas between worksheets using VBA without the macro creating hardcoded references to the original sheet.

How to Preserve Relative Formulas When Copying Ranges in Excel VBA

Product: Excel
Device & OS: not provided
Scenario: Moving or copying named ranges from a template worksheet to an event workbook using VBA automation.
Observed behavior: Manual copying adjusts the relative formulas correctly, but automating the process with VBA creates unwanted references linking back to the original source worksheets.

Before you start

Before modifying and testing your VBA macros, create a sanitized copy of your workbook by removing sensitive data. This allows you to safely test layout and formula changes without risking your primary data.

Solution 1Recommended

Use PasteSpecial Instead of Standard Copy

Using the PasteSpecial method in VBA forces Excel to paste formulas specifically, which often mimics the relative adjustment seen during a manual copy and paste.

A standard one-line VBA copy command (e.g., Range.Copy Destination:=...) can sometimes drag the source worksheet references along with it. Separating the action into a Copy and a PasteSpecial step provides better control over how formulas behave.

Locate your Copy command

Open the VBA Editor (Alt + F11) and find the line in your macro where the range is being copied.

Modify the code to use PasteSpecial

Change the single-line copy destination code to a two-line structure. First, use 'Range("YourRange").Copy'.

Apply PasteSpecial for Formulas

On the next line, add the paste operation: 'Range("Destination").PasteSpecial Paste:=xlPasteFormulas'.

Clear Clipboard

Add 'Application.CutCopyMode = False' after pasting to clear the clipboard and prevent memory leaks.

Use PasteSpecial Instead of Standard Copy

Use the Cut Method for Moving: If you are strictly moving the layout and data instead of duplicating it, using the .Cut method followed by .Insert may preserve references perfectly without formula rewriting.

Solution 2

Rewrite Formulas Using VBA Replace

If PasteSpecial still includes the original worksheet name in the relative formulas, you can programmatically strip the sheet name reference using the VBA Replace function.

Solution 3

Utilize Power Query for Data Transfer

If your primary goal is a repeatable data transfer rather than moving specific layouts, Power Query is a more robust alternative to VBA macros.

Efficient Macro Handling in WPS Office

Manage VBA Macros Easily with WPS Spreadsheet

WPS Office provides robust support for VBA and macros, allowing you to seamlessly execute scripts that copy and paste ranges with complex formulas. It offers a familiar interface, ensuring your existing VBA scripts run smoothly.

1. Enable Macros in WPS: Open your macro-enabled workbook in WPS Spreadsheet. A security prompt will appear; click 'Enable Macros' to allow scripts to run.
2. Access the VBA Editor: Navigate to the Developer tab on the ribbon and click on 'Visual Basic', or simply press Alt + F11 on your keyboard.
3. Modify Your Code: Locate your module in the Project Explorer, modify your copy commands to use PasteSpecial or Replace methods.
4. Run the Script: Save your code and click the 'Run Sub/UserForm' button (or press F5) to execute the range copy seamlessly.

Full support for standard VBA macros and scriptsHighly compatible with Microsoft Excel (.xlsx and .xlsm) formatsLightweight application with fast macro execution speedsFree and intuitive user interface with easy migration

microsoft office alternative - wps office

Frequently Asked Questions

Why does VBA copy formulas with references to the original sheet?

When copying ranges across sheets in VBA using basic destination copy methods, Excel often hardcodes the reference to the source sheet to strictly maintain the exact data link, unlike a manual paste which adapts relatively by default.

Can I remove sheet names from formulas using VBA?

Yes. You can write a VBA script to use the '.Replace' method on the destination range, searching for the source sheet name (e.g., 'Sheet1!') and replacing it with an empty string ("").

Is Power Query better than VBA for moving data?

If your goal is to repeatedly transfer clean data dynamically without needing to preserve precise layout formatting or formulas, Power Query is generally more robust, easier to maintain, and less prone to reference errors than VBA macros.

Excel & Data Problems