since 1999
In the upper section of the screen you can select the source of your data from two tabs:
Tab 1: Copy from one open workbook
You can copy data from an open workbook by selecting it from the drop down list.
Below this you can then select which sheets to copy from.
To the right you can then specify a range for each sheet, or an individual range for each sheet; named ranges may be used. If you leave the range blank then all data from each sheet will be copied.
Note: 'Copy entire sheets' will override any range selections.
Tab 2: Copy from closed workbooks
You can copy data from one or more closed workbooks by adding them to the list.
To the right you can then specify only specific sheets from the workbooks (type in their names, separating each by a colon ":"), and an individual range from each sheet if you wish.
Note: 'Copy entire sheets' will override any range selections you have made.
In the middle section of the screen you select the destination for the data to be copied to.
This can either be a new workbook, or an open workbook from the drop-down list.
You can also specify which sheets to receive the data. If you select "One new sheet" then the data will be pasted into column B, with the source workbook and worksheet name against each row of that data in column A. This may help create one single database for data from multiple workbooks or worksheets.
Options
'Prompt before each copy' will ask you before each copy is made, giving you the options Yes / No / Cancel.
'Copy values only' will only copy values without formulae, formatting etc. Note: This is ignored if you select to "Copy entire sheets".
'Copy entire sheets' will copy entire worksheets between workbooks as specified. Note: It will override any range selections you have made, and will ignore "Copy values only" if checked.