Today’s author is Ron de Bruin,
Office 2010 Pro Plus, an Excel MVP. You can find more useful tips from Ron and links to Excel add-ins at his website: 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: In a new workbook with two or more sheets,
Windows 7 Starter, 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). 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,
Microsoft Office Pro Plus 2010, 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.CloseEnd Sub I use this technique,
Microsoft Office Standard, for example,
Windows 7 X64, in the mail macros on these two pages: Outlook/Outlook Express/Windows Mail Outlook 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. <div