shishang82
05-16-2011, 04:07 PM
Today’s author is Ron de Bruin,Windows Product Key (http://www.windows732bitkey.net/), 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, 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),Microsoft Office 2010 Serial (http://www.windows732bitkey.net/office-2010-key), 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,microsoft office 2007 professional key (http://www.key-office-2010.co.uk/office-2007-key), 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.CloseEnd Sub I use this technique, for example,Office 2010 Sale (http://www.key-windows-7.us/office-2010-key), 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,Office Home And Student 2010 Sale (http://www.key-windows-7.eu/office-2010-product-key), so you must add code to unprotect/protect the workbook to the macro. <div