将Range复制到打开工作簿的另一工作表失败，触发Subscript out of range错误

Hey there! That Run-time error '9' (Subscript out of range) is one of the most common gotchas when referencing workbooks or worksheets in VBA—let’s figure out why your code is failing and get it working smoothly.

Common Causes & Solutions #

Let’s break down the most likely issues and how to fix them:

1. Mismatched File Extension in Workbook Names

Modern Excel defaults to saving files as .xlsx (or .xlsm if your file contains macros), but your code uses .xls (the old Excel 97-2003 format). If your Book1 and Book2 are saved as .xlsx/.xlsm (or even unsaved, where they don’t have a suffix yet), VBA can’t locate them because the name doesn’t match exactly.

• Quick Fix: Use the exact workbook name as it appears in Excel’s title bar. For example:
  • If the file is unsaved: Workbooks("Book1") (no suffix)
  • If saved as .xlsx: Workbooks("Book1.xlsx")
  • If saved as .xlsm: Workbooks("Book1.xlsm")

To confirm the exact name, open the VBA Editor, press Ctrl+G to open the Immediate Window, then type:

?Workbooks("Book1").Name ' Replace with your workbook name if needed

This will print the full, correct name of the workbook.

2. Typo or Case Mismatch in Worksheet Names

While Windows Excel is mostly case-insensitive with worksheet names, typos (like Sheet 1 instead of Sheet1, or sheet1 with lowercase) will still trigger this error. Double-check that both workbooks have a worksheet named exactly Sheet1 (no spaces, correct capitalization).

3. Use Object References for More Reliable Code

A smarter approach to avoid these issues is to assign workbooks/worksheets to object variables. This makes your code easier to debug and read:

Sub Range_Copy_Example()
    Dim sourceWB As Workbook
    Dim targetWB As Workbook
    Dim sourceWS As Worksheet
    Dim targetWS As Worksheet
    
    ' Set references to your open workbooks
    Set sourceWB = Workbooks("Book1") ' Use the exact name from the title bar
    Set targetWB = Workbooks("Book2")
    Set sourceWS = sourceWB.Worksheets("Sheet1")
    Set targetWS = targetWB.Worksheets("Sheet1")
    
    ' Option 1: Copy just the value (faster, no formatting)
    targetWS.Range("A1").Value = sourceWS.Range("A1").Value
    
    ' Option 2: Copy value + formatting (matches your original code)
    ' sourceWS.Range("A1").Copy Destination:=targetWS.Range("A1")
End Sub

If an error occurs here, you’ll immediately know which object (workbook or worksheet) isn’t being found.

内容的提问来源于stack exchange，提问作者Timothy Vrancic