VBA & Macro Problems

Fix VBA Error 9: Subscript Out of Range When Copying Worksheets in Excel

Algirdas Jasaitis Oct 12, 2026 870 views

Question details

The user encounters a Run-time error 9 (Subscript out of range) in VBA when attempting to copy a specific worksheet from a source workbook to a newly created and saved destination workbook.

How to Fix VBA Run-time Error 9 When Copying a Worksheet Between Excel Workbooks

Product: Microsoft Excel
Device & OS: not provided
Scenario: Automating the copying of a worksheet to a different open workbook using VBA macros.
Observed behavior: Execution stops and highlights the copy command, throwing Run-time error 9 because VBA cannot properly locate or reference the destination workbook or worksheet.

Before you start

Verify that both your source and destination workbooks are open in the same Excel instance, and ensure your Windows Folder Options are set to show file extensions so you can confirm whether the files are .xlsx or .xlsm.

Solution 1Recommended

Use Fully Qualified Workbook and Worksheet References

Resolve the out of range error by explicitly defining both the source and destination workbooks using object variables and exact file extensions.

VBA Run-time error 9 often occurs when a script references a workbook name that does not perfectly match the open file's name in the Excel title bar. This includes missing file extensions or incorrectly referencing the active worksheet instead of the target destination.

Check the exact file extensions

Look at the Excel Title Bar for both workbooks. Confirm whether the source and destination files end in .xlsx, .xlsm, or another extension. Adjust your VBA code strings to match these exactly.

Declare workbook variables

In your VBA Editor, create variables for the source and destination workbooks. For example: Dim source As Workbook and Dim dest As Workbook.

Assign the open workbooks

Set the variables using the exact names. Type: Set source = Workbooks("DISTRICT Check Page for Final Report.xlsx") and Set dest = Workbooks("DistCheckerCompiled.xlsx").

Execute the copy command

Update your copy syntax to fully qualify both the sheet being copied and the destination placement: source.Sheets("CHECK PAGE").Copy After:=dest.Sheets(dest.Sheets.Count).

Use Fully Qualified Workbook and Worksheet References

Best Practice: Always prefix Sheets.Count with your destination workbook variable (e.g., dest.Sheets.Count). Using Sheets.Count alone relies on the ActiveWorkbook, which may cause an error if the destination workbook is not currently active.

Solution 2

Set the Destination Workbook Immediately Upon Creation

If your macro creates the destination workbook dynamically, bypass string references by assigning it directly to an object variable upon creation.

Free Microsoft Office alternative

Try WPS Office for Seamless Spreadsheet Management

Struggling with complex VBA errors and macro troubleshooting? WPS Office is a lightweight, highly compatible alternative to Microsoft Office. It offers a familiar interface, built-in features to effortlessly move or copy worksheets without writing code, and robust support for your everyday spreadsheet tasks.

1. Download and Install: Get WPS Office for free from the official website and follow the quick installation process.
2. Open Your Workbooks: Launch WPS Spreadsheet and open your existing .xlsx or .xls files directly.
3. Manage Worksheets Visually: Right-click any worksheet tab and select 'Move or Copy' to duplicate sheets across different workbooks easily, bypassing the need for macros.

Fully compatible with Microsoft Excel formats (.xlsx, .xls, .csv)Free and lightweight with a familiar, easy-to-use tabbed interfaceEasily copy and manage worksheets without relying on complex codingSeamless migration: Open your existing files instantly with zero data loss

Frequently Asked Questions

What causes Run-time error 9 (Subscript out of range) in Excel VBA?

This error occurs when VBA attempts to access a collection member (like a Worksheet or Workbook) or an array index that does not exist. In copying worksheets, it is usually caused by misspelled workbook names, incorrect worksheet tabs, or omitting the file extension.

Does the destination workbook need to be saved before copying a sheet via VBA?

No, you can copy a sheet to an unsaved newly created workbook. However, if you attempt to reference it using a name like 'Book1.xlsx' before saving, it will fail because an unsaved file does not yet have the .xlsx extension attached.

Why does Sheets(Sheets.Count) fail when copying to another workbook?

If you just write 'Sheets.Count', VBA assumes you are referring to the active workbook. If your source workbook is active but you are copying the sheet to the destination workbook, the count might exceed the number of sheets in the destination, resulting in an out of range error. Always qualify it as 'dest.Sheets(dest.Sheets.Count)'.

Excel & Data Problems