VBA & Macro Problems

How to Fix Excel VBA Macro Runtime Error 1004 Caused by Merged Cells

Huma Ashraf Ch Sep 27, 2026 871 views

Question details

The user needs to resolve a macro failure that occurs when attempting to create sequential worksheets and write linked summary data into a range containing merged cells.

How to Fix Excel VBA Macro Runtime Error 1004 Caused by Merged Cells

Product: Excel
Device & OS: not provided
Scenario: Running a VBA macro designed to read a count, generate sequential worksheets with blank rows between entries, and link summary cells to the new worksheets.
Observed behavior: The macro execution fails and throws 'Runtime error 1004: We cannot do that to a merged cell'.

Before you start

Before modifying your macro or altering the worksheet structure, save a backup copy of your file to prevent any accidental data loss while troubleshooting.

Solution 1Recommended

Unmerge Target Cells in the Worksheet

The most straightforward solution is to remove the merged cells from the specific range where the VBA macro is attempting to write or link data.

Excel VBA generally struggles to write array data or perform standard paste operations into merged cells. Unmerging the destination area allows the macro to process the sequential entries and blank rows without interruption.

Select the target range

Open your summary worksheet and highlight the specific cells or columns where the macro is configured to output data.

Unmerge the cells

Navigate to the Home tab on the top ribbon, locate the Alignment group, and click the 'Merge & Center' button to toggle off the merge formatting.

Rerun the macro

Once all target cells in the output range are unmerged, run the VBA macro again to verify the Runtime Error 1004 is resolved.

Alternative Formatting: If you want to maintain the visual appearance of merged cells without breaking your macro, select the cells, right-click to choose 'Format Cells', navigate to the Alignment tab, and select 'Center Across Selection' from the Horizontal dropdown.

Solution 2

Modify the VBA Macro Output Range

If the merged cells are strictly required for your worksheet layout, you can adjust the VBA code to write data to a different, unmerged column.

Run and Debug Macros Seamlessly in WPS Spreadsheet

WPS Office provides robust VBA support, allowing you to easily run, edit, and debug macros. If you encounter merged cell errors, you can quickly adjust your VBA code or worksheet layout within its highly compatible environment.

1. Open your macro-enabled file: Launch WPS Spreadsheet and open your .xlsm file containing the macro.
2. Access the Developer tools: Navigate to the Developer tab on the top ribbon. If it is not visible, enable it in the Options menu.
3. Modify cells or code: Select the merged cells and click 'Merge and Center' on the Home tab to unmerge them, or click 'Visual Basic' to edit the macro output range.
4. Execute the macro: Run your updated macro directly from the Developer tab to generate your sequential worksheets error-free.

Full support for running and debugging VBA macros100% compatible with Microsoft Excel (.xlsx, .xlsm) formatsIntuitive cell formatting and unmerging toolsFree and lightweight alternative for spreadsheet management

microsoft office alternative - wps office

Frequently Asked Questions

What does Runtime Error 1004 mean in Excel VBA?

Runtime Error 1004 is a generic error code in Excel VBA that often occurs when a macro tries to perform an invalid action, such as writing data into a range containing merged cells, or referring to a worksheet or range that does not exist.

How can I center text across columns without merging cells?

Select the adjacent cells you want to center text across. Right-click and choose 'Format Cells', go to the 'Alignment' tab, click the 'Horizontal' drop-down menu, and select 'Center Across Selection'. This keeps the cells functionally separate for VBA macros while looking identical to merged cells.

Why does my VBA macro work on one worksheet but fail with Error 1004 on another?

This happens when the layout structure of the worksheets differs. If your macro tries to output data into a worksheet where specific cells have been merged, it will throw Error 1004, whereas the same code will execute perfectly on an identical worksheet that contains no merged cells in the target range.

Excel & Data Problems