Windows 7 Home Premium Sale Copying Worksheets wit

Today’s author is Ron de Bruin,Office 2010 Standard X86, 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), 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",Windows 7 Home Premium Sale, "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,Cheap Windows 7 Key, 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",Microsoft Office 2010 Key, "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 2007 Professional Product 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, so you must add code to unprotect/protect the workbook to the macro. <div