Forum Discussion
Combining workbooks/worksheets
Hi,
If you don't want to use VBA, then you have to move each worksheet manually by following these steps:
Combine Multiple Workbooks To One Workbook With Move Or Copy Command
If you are a rookie of Microsoft Excel, you have no choice but only have to copy the data of every sheet and paste them in to a new workbook one by one and applying the Move or Copy command. Using the Move or Copy command will help you export or copy one or several worksheets to a new workbook quickly.
1. Open all workbooks that you want to merge into a single workbook.
2. Select all of the worksheet names of a workbook in tab bar. You can select multiple with holding down Ctrl key or Shift key. Right click the worksheet name, and click the Move or Copy from context menu.
3. In Move or Copy dialog box, select the master workbook that you want to merge other workbooks into from the drop down list of Move selected sheets to book. And then specify the location of the merged worksheets. See screenshots:
 |  |  |
4. Then click OK. The selected worksheets have been moved to the master workbook.
5. Repeat the steps from 2 to 4 to move other workbooks to the master workbook. Then it combines all worksheets of opened workbooks into a single workbook. See screenshots:
 |
 |
 |
The source:
https://www.extendoffice.com/documents/excel/456-combine-multiple-workbooks.html
Hi Haytham--thanks for the reply! That's the way I would have originally done it, but there are going to be a large number of workbooks that need to be added.
Maybe VBA is the best way to do it?
There is already a workbook that exists with about 3 sheets and I want to add multiple excel files that are borough specific to that "master" workbook (i.e. add the workbooks as separate tabs within that master workbook).
Hi,
Please note that the link I mentioned in the previous reply guides you to three methods as follows:
- Combine Multiple Workbooks To One Workbook With VBA.
- Combine Multiple Workbooks To One Workbook With Move Or Copy Command.
- Combine Multiple Workbooks To One Workbook With Kutools For Excel (Which is a third party Excel Add-in).
You can choose the method that suits you best.
The VBA maybe the best way, but provided that the code is good, and the VBA code in that link is fairly good.
But the problem in that code is:
It's combine all worksheets in the workbooks into the master workbook, so you cannot specify certain worksheets of the workbooks to combine.
in a related question, is it possible to take the sheets from several excel workbooks to create new ones, for example;
Workbook 1; Sheet 1, Sheet 2, Sheet 3
Workbook 2; Sheet 21, Sheet 22, Sheet 23
Workbook 3; Sheets 31, 32 & 33.
What I want is;
New Workbook 1; Sheet 1, Sheet 21, Sheet 31
New Workbook 2; Sheet 2, 22, 32
New Workbook 3; Sheet 3, 23, 33.
We have a system for monitoring costs which can export data for each section to excel but in order to produce legible reports, these must be broken down and collated in the fashion outlined above; is there a way of automating this?
