Microsoft KB Archive/161206

= Microsoft Knowledge Base =

XL: Error Using a Macro to Copy Data from Grouped Sheets
Last reviewed: July 9, 1997

Article ID: Q161206

The information in this article applies to:


 * 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. and the macro halts.

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.

Grouped sheets in the source workbook cause Microsoft Excel to group the same number of sheets in the destination workbook. If there are not enough sheets in the destination workbook to group, you receive the error message as described in this article.

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.

This workbook is the source workbook.

  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  Switch to Dbook.xls.

If you created a new workbook, save the file before continuing.


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

NOTE: The macro will copy the same range from each of the selected sheets.


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

The macro copies the data from the ungrouped sheets. You must save both workbooks when you are finished.

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.

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 and then double-click the selected text to go to the "Select sheets in a workbook" topic.

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 "User's Guide," version 5.0, Chapter 7, "Grouping Worksheets for Fast Data Entry, Editing, and Formatting"