How to copy two sheets to a new workbook?

user_7881804 0
Viewed 88

I have a workbook with many sheets. I'm trying to copy two sheets together to a new workbook.

I get

Run-time error 13 for type mismatch.

Sub CopyBillStatandCosts()
    Dim MyBook As Workbook
    Dim NewBook As Workbook

    Set MyBook = ThisWorkbook
    Workbooks.Add ' Open a new workbook
    Set NewBook = ActiveWorkbook

    Set MyBook = ActiveWorkbook

    Sheets(11).Copy Before:=Workbooks(NewBook).Sheets(1)
    Sheets(9).Copy Before:=Workbooks(NewBook).Sheets(1)
    Workbooks(NewBook).Sheet1.Delete
End Sub

Update: I figured out the code. But how do I refer to the sheets by their code names, which is best practice? They are sheet9 and sheet 11.

Sub copyBillStatandCosts()
    ThisWorkbook.Worksheets(Array("BillStat", "C")).Copy
End Sub
vba excel
3 Answers

Your second

Set MyBook = ActiveWorkbook

was probably meant to be

MyBook.Activate

although an overall simpler way to do this would be

Sub CopyBillStatandCosts()
    Sheets(Array("BillStat", "Costs")).Copy
End Sub

The Copy with no parameter makes the copy in a new workbook.

user_44754680

Copy Worksheets by Code Name in One Go

Sub copyBillStatandCosts()

    ThisWorkbook.Worksheets(Array(Sheet9.Name, Sheet11.Name)).Copy

    ' To continue to work with the new workbook, do the following:
    Dim NewBook As Workbook: Set NewBook = ActiveWorkbook
    ' e.g.:
    ' NewBook.SaveAs "C:\Test\Test.xlsx", xlOpenXMLWorkbook

    ' To continue to work with each new worksheet, do the following: 
    Dim bws As Worksheet: Set bws = NewBook.Worksheets(1)
    Dim cws As Worksheet: Set cws = NewBook.Worksheets(2)
    ' e.g.:
    MsgBox NewBook.Name & vbLf & bws.Name & vbLf & cws.Name

End Sub
  • Why use code names? Now you can rename the two worksheets in the tabs, and the code will still copy the right ones to a new workbook.
  • Why in one go? If there are references of the worksheets from one to each other they will still work in the new workbook i.e. will not refer to the worksheets in the source workbook.
user_98140690

If you wanna use programmatic names, then just use Name property:

ThisWorkbook.Sheets(Array(sheet9.Name, sheet11.Name)).Copy
user_31870330
Related