Today’s author is Ron de Bruin, an Excel MVP. You can find more useful tips from Ron and links to Excel add-ins at his website: http://www.rondebruin.nl/

Problem:

It is not possible to copy more than one sheet to a new workbook or existing workbook if there is a List (Excel 2003) or a Table (Excel 2007) in one of the worksheets.

How to reproduce:

  1. In a new workbook with two or more sheets, add a table or a list in one of the worksheets.
  2. Select more than one sheet with the CTRL key held down. (Be sure that one of the selected worksheets contains a List or Table.)
  3. Right-click on one of the sheet tabs, and then choose Move or copy.
  4. In To Book, choose (new book), and then check the checkbox named Create a Copy.
  5. Click OK, and then you will see a message box: “You cannot copy or move a group of sheets that contain a table.”

The only manual way to work around this is to do it in two or more steps. First copy the sheets without a List or Table, and then copy each sheet that contains a List or Table one-at-a-time.

How to reproduce with VBA code:

When you use VBA code like the following to copy more than one sheet to a new workbook you will get a 1004 error if you have more than one sheet selected and one of the sheets contains a List (Excel 2003) or Table (Excel 2007 and up).

Sheets(Array("Sheet1", "Sheet2")).Copy

Workaround:

In the macro below, I show you a workaround that enables you to copy the sheets and to avoid the error.

The solution is to add a new Window with the Workbook.NewWindow method, If you add a new window, the sheets are not grouped anymore in this window. If the sheets are not grouped, Excel will copy the sheets correctly into a new workbook.

Sub Copy_Worksheets()
    Dim TheActiveWindow As Window
    Dim TempWindow As Window
    With ActiveWorkbook
        Set TheActiveWindow = ActiveWindow
        Set TempWindow = .NewWindow
        .Sheets(Array("Sheet1", "sheet2")).Copy
        'If you want to copy the selected sheets use
        'TheActiveWindow.SelectedSheets.Copy
    End With
    TempWindow.Close
End Sub

I use this technique, for example, in the mail macros on these two pages:

Outlook/Outlook Express/Windows Mail

http://www.rondebruin.nl/mail/folder1/mail3.htm

Outlook

http://www.rondebruin.nl/mail/folder2/mail3.htm

Important: If you protect your workbook (using Tools > Protection > Protect Workbook with Windows checked in Excel 2003, or Review > Protect Workbook >Protect Structure and Windows with Windows checked in Excel 2007), the macro code can't add a temporary window to the workbook, so you must add code to unprotect/protect the workbook to the macro.