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:
- In a new workbook with two or more sheets, add a table or a list in one of the worksheets.
- Select more than one sheet with the CTRL key held down. (Be sure that one of the selected worksheets contains a List or Table.)
- Right-click on one of the sheet tabs, and then choose Move or copy.
- In To Book, choose (new book), and then check the checkbox named Create a Copy.
- 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).
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
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.