VBA & Macro Problems

Fix Excel VBA Paste Values Error with Merged Cells and Hyperlinks

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to copy an entire row and paste only the values using an Excel VBA macro, while preserving the existing merged cells, formatting, and hyperlinks.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Running a VBA macro to copy a row containing formulas, hyperlinks, and merged cells, then pasting the data as static values elsewhere in the sheet.
Observed behavior: The macro works for a single cell but fails with a range size mismatch error when copying a row due to merged cells. Additionally, a standard values-only paste removes all formatting and hyperlinks.

Before you start

Before modifying and testing your VBA scripts, create a sanitized copy of your workbook without confidential data to safely test the macro without risking data corruption.

Solution 1Recommended

Match Merged Cell Layouts and Use a Multi-Step Paste

Align the merged cells in both the source and destination ranges, then use a two-step pasting process in VBA to keep formatting and hyperlinks while converting formulas to values.

A direct values-only paste in Excel inherently strips out hyperlinks and cell formatting. Furthermore, copying and pasting across ranges with different merged-cell structures will trigger a size mismatch error. To bypass this, the destination must mirror the source's structure exactly, and the paste must be done in sequential steps.

Standardize the destination range

Ensure the destination range in your worksheet has the exact same merged cell structure and dimensions as the source range before executing the copy command.

Copy the source data

Use the `Range.Copy` method in your VBA code to copy the target row containing the formulas and hyperlinks.

Paste formats and hyperlinks first

Apply `Range.PasteSpecial Paste:=xlPasteAll` or `Paste:=xlPasteFormats` to the destination range. This transfers the structural layout, visual formatting, and embedded hyperlinks.

Overwrite formulas with static values

Immediately follow up with `Range.PasteSpecial Paste:=xlPasteValues` on the exact same destination range to replace the copied formulas with static text values without removing the previously pasted formatting.

Values-Only Paste Limitations: Using xlPasteValues alone will always delete hyperlinks. The sequential paste method is required to separate the formatting transfer from the value conversion.

Solution 2

Reapply Hyperlinks Explicitly Using VBA Loop

If standard pasting methods fail to retain hyperlinks, extract the link addresses programmatically and reapply them to the destination range after pasting values.

Advanced VBA Support in WPS Spreadsheet

Handle Macros and VBA Seamlessly with WPS Office

WPS Spreadsheet offers robust, built-in support for VBA and macros, allowing you to run, edit, and debug your automated scripts with ease. You can transition your Excel macros directly to WPS Office without having to rewrite your code.

1. Open your macro workbook: Launch WPS Spreadsheet and open your existing macro-enabled workbook (.xlsm).
2. Access the Developer tools: Navigate to the 'Developer' tab located on the top ribbon menu.
3. Launch the VBA Editor: Click the 'Visual Basic' icon or press ALT + F11 to open the integrated VBA editor.
4. Run and test your code: Paste or modify your macro code to handle the merged cells, then click 'Run' to seamlessly execute the script within your spreadsheet.

Fully compatible with Microsoft Excel macro-enabled formats (.xlsm, .xlsb).Built-in Visual Basic editor for writing, editing, and debugging VBA code natively.Lightweight architecture ensures complex scripts run smoothly without lag.Easily handle advanced formatting and merged-cell automations.

Frequently Asked Questions

Why do I get a range size error when pasting in VBA?

This error occurs because the destination range has merged cells that do not perfectly match the dimensions, size, and layout of the merged cells in the copied source range. Both ranges must have identical merged structures to allow a successful paste operation.

Does pasting as values in Excel VBA keep my hyperlinks?

No, using `xlPasteValues` natively strips all formatting, hyperlinks, and data validation, leaving only the static text or formula results. To keep hyperlinks, you must execute a standard copy-paste or paste-formats command first, and then overwrite the formulas with values.

How can I safely test my VBA macro without ruining my data?

You should create a sanitized test workbook by duplicating your original file and deleting any sensitive or confidential information. Run your new VBA macros on this dummy file to ensure it correctly handles the merged cells before applying it to your actual dataset.

Excel & Data Problems