Microsoft Knowledge Base

• Microsoft Excel 97 for Windows
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a

SYMPTOMS

When you run a Visual Basic for Application macro that copies data from multiple selected worksheets to a single worksheet in another workbook, the following error message may appear:

   Run-time error '1004':
   Copy and paste areas are different shapes.

CAUSE

This problem occurs when the following conditions are true:

• You select multiple sheets in the source workbook.

  -and-

• There are fewer worksheets in the destination workbook than there are selected sheets in the source workbook.

WORKAROUND

Microsoft provides examples of Visual Basic for Applications procedures for illustration only, without warranty either expressed or implied, including, but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft Product Support Services (PSS) Engineers can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.

To work around this behavior, ungroup the sheets before you run the macro, but preserve the range you want to copy and the sheets that are selected.

The following example copies the selected range from sheets that are selected in the source workbook and pastes the information into the first sheet in the specified destination workbook:

1. Create a workbook with three worksheets and save it as Sbook.xls.

1. Insert a module sheet by clicking Macro on the Insert menu and clicking Module. On this module sheet, type the following macro:

         Sub CopyFromGroupedSheets()
   
             'Set up variables for the destination cell in dbook.xls,
             'number of rows in the copy range, the active sheet name,
             'and the currently grouped sheets.
             xrange = Windows("dbook.xls").ActiveCell.Address
   
             'To specify a destination cell, use the following line instead
             'of the above line:
             'xrange = "A1"
             xrows = Selection.Rows.Count
             mysheet = ActiveSheet.Name
             Application.ScreenUpdating = False
             Set mysheets = ActiveWindow.SelectedSheets
   
             For Each sht In mysheets
                 'Activate each sheet from which to copy and break the
                 'group mode.
                 sht.Activate
                 'Copy the selection and paste it to the destination.
                 'The data copied from each sheet is pasted below
                 'previously copied data.
                 Selection.Copy Workbooks("dbook.xls").Worksheets("Sheet1"). _
                     Range(xrange).Offset(y * xrows, 0)
                 y = y + 1
             Next
             'Regroup the sheets and activate the original active sheet.
             mysheets.Select
             Sheets(mysheet).Activate
   
         End Sub
   
   

2. Switch to Dbook.xls.

1. Group the sheets in Sbook.xls that contain the data you want to copy by holding down CTRL and clicking the individual sheet tabs.

2. On the active sheet, select the range you want to copy from each selected sheet.

1. On the Tools menu, click Macro. Click the CopyFromGroupedSheets macro in the list of available macros and click Run.

MORE INFORMATION

If the destination workbook contains an equal or greater number of worksheets, the macro copies the data in the specified range from the first sheet in the source workbook to the first sheet in the destination workbook. Note that the macro duplicates the data as many times as the number of sheets you select. The data on the second sheet is placed on the second sheet, and so on.

Microsoft Excel groups the sheets. This group starts with the active sheet and includes sheets to the right of the active sheet. If there are not enough sheets to the right of the active sheet in the destination workbook, you receive the error message described in this article.

REFERENCES

Microsoft Excel 97

For more information about selecting sheets in a workbook, click the Index tab in Microsoft Excel Help, type the following text

   worksheets, selecting sheets

Microsoft Excel 5.0 or 7.0

For more information about Select Sheets in a Workbook in Microsoft Excel versions 5.0 and 7.0, click Help Topics on the Help menu and type:

   sheets, selecting