Excel工作簿工作表复制问题求助：Copy方法执行失败

Let’s walk through why you’re seeing that random Book1 pop up and the "Copy method of Worksheet class failed" error, plus how to fix it step by step:

1. The Root Cause of Book1 #

When you call Worksheet.Copy without specifying a Before or After target location, Excel automatically creates a new workbook (that’s your Book1) to hold the copied sheet. This is default behavior—but it’s not what you want here. You need to explicitly tell Excel to copy the sheet into your xBook instead.

2. Fix the Core Copy Syntax #

First, confirm your variable references are correct. If o.Book is a valid reference to your source workbook (double-check it’s not a typo like oBook instead), use this syntax to copy directly into xBook:

' Copy the "general report" sheet to the end of xBook
o.Book.Worksheets("general report").Copy After:=xBook.Worksheets(xBook.Worksheets.Count)

Or if you want it placed at the start of xBook:

o.Book.Worksheets("general report").Copy Before:=xBook.Worksheets(1)

3. Check for Common Blockers #

Even with the right syntax, these issues can still trigger the error:

• Protected Source Sheet: If "general report" is protected, Excel can’t copy it. Unprotect it temporarily if needed (re-protect afterward if desired):

  With o.Book.Worksheets("general report")
      If .ProtectContents Then
          .Unprotect Password:="YourSheetPassword" ' Add your password if set
          .Copy After:=xBook.Worksheets(xBook.Worksheets.Count)
          .Protect Password:="YourSheetPassword"
      Else
          .Copy After:=xBook.Worksheets(xBook.Worksheets.Count)
      End If
  End With
  

• Duplicate Sheet Name in xBook: If xBook already has a sheet named "general report", the copy will fail. Check for duplicates and handle them (e.g., delete the old sheet or rename the new one):

  Dim ws As Worksheet
  Dim sheetExists As Boolean
  sheetExists = False
  
  For Each ws In xBook.Worksheets
      If ws.Name = "general report" Then
          sheetExists = True
          Exit For
      End If
  Next ws
  
  Application.DisplayAlerts = False ' Suppress delete confirmation prompt
  If sheetExists Then xBook.Worksheets("general report").Delete
  o.Book.Worksheets("general report").Copy After:=xBook.Worksheets(xBook.Worksheets.Count)
  Application.DisplayAlerts = True
  

• Read-Only Workbooks: Ensure both source and target workbooks are opened in edit mode (not read-only). When initializing them, confirm:

  Set o.Book = Workbooks.Open("C:\Path\To\YourSourceFile.xlsx", ReadOnly:=False)
  Set xBook = Workbooks.Open("C:\Path\To\YourTargetFile.xlsx", ReadOnly:=False)
  

4. Verify Object References #

Double-check that o.Book is actually pointing to the correct open workbook. If you defined o as a custom object, make sure its Book property is properly initialized to the source workbook (not Nothing or the wrong file). If it’s a typo and you meant a variable named oBook, correct that first!

If you still hit snags, share a snippet of how you’re initializing o.Book and xBook—that’ll help narrow down the issue further.

内容的提问来源于stack exchange，提问作者Laszlo Malina