VBA & Macro Problems

Fix Excel Closes When Running VBA Macro on Multiple Worksheets

Ayan Masood Oct 23, 2026 868 views

Question details

The user needs to resolve an issue where Excel unexpectedly crashes and closes while executing a VBA macro that loops through multiple worksheets.

How to Fix Excel Closing When Running a VBA Macro on Multiple Worksheets

Product: Microsoft Excel 2016
Device & OS: Windows
Scenario: Running an automated VBA macro that involves looping through multiple sheets, extensive copying, formatting, and the use of Select and ActiveCell commands.
Observed behavior: The macro executes successfully on a single worksheet, but causes the entire Excel application to close abruptly when attempting to process multiple sheets in a loop.

Before you start

Before modifying your VBA code, always save a backup copy of your workbook. Additionally, consider disabling screen updating and automatic calculations at the beginning of your script to reduce system resource usage.

Solution 1Recommended

Optimize VBA Code by Removing Select and ActiveCell

Refactoring your VBA code to use direct object references instead of physical selections significantly reduces memory overhead and prevents Excel from crashing.

Using 'Select', 'Selection', and 'ActiveCell' forces Excel to constantly update the graphical user interface during loops. When processing multiple worksheets rapidly, this excessive UI rendering often overwhelms application memory, causing Excel to close unexpectedly.

Open the VBA Editor

Press ALT + F11 to launch the Visual Basic for Applications (VBA) editor and locate the macro causing the issue.

Identify Inefficient Code

Scan your code for lines utilizing 'Select', 'Selection', 'ActiveCell', or workbook and worksheet activations.

Apply Direct References

Replace selection-based actions with direct references. For example, instead of writing 'Sheets("Sheet1").Select' followed by 'Range("A1").Select', use 'Worksheets("Sheet1").Range("A1").Value'.

Restrict Formatting Scope

Avoid using 'Cells.Select' to format entire worksheets. Limit formatting operations and copy-paste tasks to the specific data ranges using direct range assignments, such as arrays, to improve stability.

Optimize VBA Code by Removing Select and ActiveCell

Performance Boost: By removing selection commands, your macro will not only stop crashing but will also execute significantly faster.

Solution 2

Implement a 'For Each' Loop Structure

Using a robust 'For Each' loop ensures Excel handles worksheet iterations smoothly without dropping references or causing memory leaks.

Solution 3

Test Macro in a New Windows User Profile

If the code is fully optimized and the crash persists, testing on a new profile helps identify corrupted environment variables.

Free Microsoft Office alternative

Experience Stable Spreadsheet Operations with WPS Office

If Microsoft Excel continues to crash or struggle with complex automated tasks, consider switching to WPS Office. It provides a highly compatible, stable environment that handles extensive operations smoothly without draining your system resources.

1. Download and Install: Download WPS Office for free from the official website and follow the installation prompt.
2. Open Your Workbook: Launch WPS Spreadsheet and open your existing .xlsm file directly.
3. Run Operations Smoothly: Enjoy a more stable spreadsheet experience when handling large datasets and automated logic.

Fully compatible with Microsoft Excel file formats, including .xlsx and .xlsm macro-enabled workbooks.Lightweight architecture that minimizes memory consumption during heavy computational tasks and data processing.Familiar user interface ensuring a seamless migration and zero learning curve.Completely free to download and use for your essential productivity needs.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel crash when using 'Select' in a VBA loop?

Using 'Select' or 'ActiveCell' forces Excel to repeatedly update the graphical user interface and shift active focus. In a rapid loop across multiple sheets, this overwhelms the application's memory allocation, causing it to freeze or close unexpectedly.

How can I quickly improve the performance of an Excel macro processing multiple sheets?

You can dramatically improve performance by adding 'Application.ScreenUpdating = False' and 'Application.Calculation = xlCalculationManual' at the start of your code, ensuring you revert them to True and xlCalculationAutomatic at the end. This prevents Excel from rendering visual changes during the execution.

Is it bad practice to apply formatting to entire columns in VBA?

Yes, applying formats to entire columns (using commands like 'Cells.Select' or 'Columns("A:A").Select') applies changes to over a million rows per sheet. This inflates the file size and processing time, which frequently leads to application crashes. Always target the specific utilized range.

What is the most stable way to iterate through worksheets in VBA?

The most stable method is utilizing a 'For Each' loop, such as 'For Each ws In ThisWorkbook.Worksheets'. This allows the code to iterate cleanly through all existing sheets by object reference, avoiding errors tied to changing index numbers or manual sheet activation.

Excel & Data Problems