Optimized Implementation and Common Error Analysis for Copying Multiple Sheets to a New Workbook in Excel VBA

Problem Background and Error Analysis

In Excel VBA development, copying multiple sheets to a new workbook is a common task, but developers often encounter the 'Object Required' runtime error. This error typically stems from improper object references, such as misusing string variables as objects. In the provided Q&A data, the original code attempts to use MyBook.Worksheets, but MyBook is a string storing the workbook name, not a Workbook object, violating VBA's object model rules and causing the error.

Core Knowledge Points

VBA's object model requires explicit references to objects like Workbook and Worksheet. String variables only contain name information and cannot directly access object properties and methods. The correct approach is to use the Set keyword to assign objects to variables, e.g., Set wb = ActiveWorkbook, then access the worksheets collection via wb.Worksheets. This ensures type safety and avoids runtime errors.

Basic Fix Implementation

The following code fixes the original error by introducing a Workbook object variable WB for proper worksheet reference:

Sub NewWBandPasteSpecialALLSheets()
   MyBook = ActiveWorkbook.Name
   Workbooks.Add
   NewBook = ActiveWorkbook.Name
   Workbooks(MyBook).Activate
   Set WB = ActiveWorkbook
   Dim SH As Worksheet
   For Each SH In WB.Worksheets
       SH.Range("WholePrintArea").Copy
       Workbooks(NewBook).Activate
       With SH.Range("A1")
        .PasteSpecial (xlPasteColumnWidths)
        .PasteSpecial (xlFormats)
        .PasteSpecial (xlValues)
       End With
     Next
End Sub

This version resolves the object reference issue but has functional limitations: it does not create new sheets in the new workbook, instead attempting to paste into cell A1 of the original sheets, which may cause data overwriting or logical errors.

Advanced Complete Implementation

To fully meet the requirements, the following code creates sheets with matching names in the new workbook and copies the print area content:

Sub NewWBandPasteSpecialALLSheets()
   Dim wb As Workbook
   Dim wbNew As Workbook
   Dim sh As Worksheet
   Dim shNew As Worksheet
   Set wb = ThisWorkbook
   Workbooks.Add
   Set wbNew = ActiveWorkbook
   On Error Resume Next
   For Each sh In wb.Worksheets
      sh.Range("WholePrintArea").Copy
      With wbNew.Worksheets
          Set shNew = Nothing
          Set shNew = .Item(sh.Name)
          If shNew Is Nothing Then
              .Add After:=.Item(.Count)
              .Item(.Count).Name = sh.Name
              Set shNew = .Item(.Count)
          End If
      End With
      With shNew.Range("A1")
          .PasteSpecial (xlPasteColumnWidths)
          .PasteSpecial (xlFormats)
          .PasteSpecial (xlValues)
      End With
   Next
End Sub

Key aspects of this implementation include: using ThisWorkbook to reference the workbook containing the code for stability; handling errors when sheets do not exist via On Error Resume Next; dynamically checking and creating new sheets; and ensuring copy operations target the correct location in the new workbook. This avoids data confusion and provides robust error handling.

Conclusion and Best Practices

When operating on multiple sheets in Excel VBA, always use object variables instead of strings for references and consider error handling to enhance code robustness. The two implementations in this article demonstrate evolution from a basic fix to a complete functionality, helping developers deeply understand VBA object models and programming patterns. It is recommended to adopt the advanced version in practical applications to ensure data integrity and code maintainability.