Back
Excel

Copying Worksheets with a List or Table

Share on Facebook Share on Twitter Share on Linkedin Share via OneNote Share via Email Print

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).

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.

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.

Top