How to combine one page in Excel. How to combine Excel files? Combine sheets into one book

Let's say you have several sales reports for different regions. If you want to perform calculations and plot graphs for the entire amount of data, you need to collect all the data into one summary sheet. Switching between multiple workbooks, endlessly copying hundreds of lines, or writing VBA can take a long time.

With the "Combine Sheets" add-in, you can collect data into one summary sheet in a matter of seconds:

  • Collect sheet data from different workbooks on one sheet
  • Collect data from worksheets with the same name and combine by tab name
  • Combine data from sheets with identical structure under one heading
  • Preserve formatting in result pivot sheet

Add "Combine Sheets" to Excel 2016, 2013, 2010, 2007

Suitable for: Microsoft Excel 2016 - 2007, desktop Office 365 (32-bit and 64-bit).

How to work with the add-in:

How to combine data from multiple sheets into one sheet in 3 steps

With the Merge Sheets add-in, you can collect and combine data from multiple sheets - and from different workbooks - into one master sheet in just 3 steps:

1. Click the Merge Sheets button on XLTools toolbar\u003e Select the type of operation:

  • Combine data from sheets with the same name in one sheet

2. Mark the sheets that are tedious to combine. The data tree displays all sheets in all open workbooks.

3. Click the Merge button\u003e Done! All data has been copied to one master summary sheet.


How to combine data from multiple sheets in one pivot sheet

Suppose you have a series of sheets, and each of them contains a sales report for a specific product category. The add-in will help you copy all these separate reports and combine the data into one pivot sheet.

  1. Click the Combine Sheets button\u003e Select Combine Multiple Sheets Data on One Sheet.

  3. Click the Combine button\u003e Done, all the data from the selected sheets is collected on one master sheet in a new workbook.


How to combine data from sheets with the same name in one pivot sheet

Suppose you have a number of books, and each of them contains a regional sales report. Each report is split into product-specific data sheets, so the tabs in regional reports have the same names. The add-in will help you copy the data for all reports into one summary sheet.

  1. Click the Combine Sheets button\u003e Select Combine Data from Same Named Sheets in One Sheet.
  2. Check the "Tables with Headers" box if so.
    Tip: this way, the data will be combined under a single heading. This is convenient if the structure of the sheets that you are merging is uniform, for example, if the reports are created according to the same template. If the headers do not match, each data range will be added with its own header.
  3. Select the sheets to be merged by checking the appropriate checkboxes in the data tree.
    Tip: instead of alternately combining sheets with the same name into groups (one noun phrase after another), you can select them all at once. The add-in will automatically collect data on the same tab names and put them on the corresponding separate sheets of the summary book.
  4. Click the Merge button\u003e Done, all the data of the selected worksheets with the same name is collected in a new summary book.


How data is copied to the master sheet

Merging data essentially means extracting and copying data from multiple original sheets to a new sheet.

  • Data is copied in full - the entire range up to the last used cell on the original sheet.
  • The copied ranges are added sequentially, one range below the last line of the previous range.
  • XLTools Merge Sheets add-in preserves cell and table formatting, cell references, functions and formulas, merged cells, and more.
  • The original data is not subject to change.

How to combine multiple sheets into one book

You can combine multiple sheets into one book using XLTools Book Organizer add-in. It helps you copy and manage multiple sheets at the same time.

Do you have any questions or suggestions? Leave a comment below.

Most often, you have to work with tabular data in the office or at home in the Microsoft Office Excel spreadsheet editor. Each file in this application contains one document, divided into separate sheets of spreadsheets. Alas, among the application commands there is no function of automatically combining several sheets of a document into one. Nevertheless, such a task is not so rare, and it has to be solved either "manually" or using scripts - "macros".

You will need

  • Tabular editor Microsoft Office Excel 2007 or 2010.

Instructions

  • If you need to combine a small number of sheets into one, it is not difficult to do this with a simple combination of copy and paste operations. Select the sheet where all tables will be merged - pivot. If it already contains data, place the insertion cursor in the first cell of the added area - in the cell of the first row to the right of the extreme column when adding data horizontally, or in the cell of the first column below the last row when adding vertically.
  • Go to the sheet, the data of which you want to add to the pivot, and click the bottom right cell with data. Press the keyboard shortcut Ctrl + Shift + Home to select the entire table. Please note that you only need to select cells with data, not the entire contents of the sheet, otherwise Excel will display an error message when inserting. Mark the copied area to the clipboard - press the key combination Ctrl + C.
  • Return to the summary sheet and paste the copied - press the "hot keys" Ctrl + V. Repeat the operations of positioning, copying and pasting as many times as necessary, if you need to combine more than two sheets. At the end of the procedure, the copied sheets can be deleted - click their tabs with the right mouse button, select the "Delete" item and press the "Yes" button in the operation confirmation dialog.
  • If there are a lot of combined sheets, you will have to use a script, i.e. place a button on the page and bind the corresponding macro to it. To do this, use the "Developer" tab. If it does not appear in the menu, right-click the free space on any tab and select Customize Ribbon. In the list of "Main Tabs" check the box next to "Developer" and click OK.
  • On the "Developer" tab, open the "Insert" drop-down list from the "Controls" group of commands and select the very first item in it - the button. Then click the mouse to specify the place in the table where you want to place the button, and the "Assign macro to object" dialog box will appear on the screen.
  • Press the "Create" button and between the first and last lines of code in the window that opens, enter, for example, the following set of commands: s_ \u003d Sheets.CountSheets.Add After: \u003d Sheets (s_) For i \u003d 1 To s_ r_ \u003d Sheets (i). Cells.SpecialCells (xlLastCell) .Row Sheets (i) .Range ("A1", Sheets (i) .Cells.SpecialCells (xlLastCell)). Copy Sheets (s_ + 1) .Range ("a" & n_ + 1) n_ \u003d n_ + r_Next Close the macro editor.
  • Click on the created button, and the macro assigned to it will create a new sheet, in which it will combine the contents of all the others, linking them vertically.
    • Rate the article!

    A small article was published on one of the pages of this site about how to save sheets of active Excel workbook to separate files... Now is the time to consider the reverse question of how to collect information from different files in general and how to combine several files into one book in particular. This can be done easily and quickly, but more on that later.

    Simultaneous user work in one book

    In some cases, several users have to collect or process information simultaneously. There are different methods to avoid transmitting spreadsheet sequentially along a chain from one user to another, and work in it simultaneously, or in parallel. One of these methods is to share the document, when users can simultaneously work with the data of the document.

    Another way to speed up the process of collecting or processing data is to divide the workbook into separate sheets and then combine them after custom processing. We have already talked about how you can divide a workbook into separate sheets and save these sheets as separate files. Now let's take a closer look at how to combine them back.

    Combine sheets of different workbooks into one

    Combining different files into one or specific sheets into one book has various solutions. This is the standard option with copying and pasting sheets into the desired workbook, and the use of macros, and the installation of additional programs specializing in file merging, such as MergeExcel. Each of these methods has its pros and cons. For my part, I want to offer another way to solve the problem of combining sheets from different books into one. Without having to deal with the program code of macros and without installing additional programs, you can quickly extend Excel with new features using an add-on called an add-in.

    The add-in for combining various files into one is based on a VBA macro, but compares favorably with its ease of use. The add-on is easy to connect and is launched by one click of a button brought directly to the main menu, after which a dialog box appears. Then everything is intuitively clear, files are selected, sheets of these files are selected, additional merging parameters are selected, and the "Start" button is pressed.


    macro (add-in) to combine multiple Excel files into one workbook

    The add-on allows:

    1. With one mouse click, open the macro dialog box directly from the Excel toolbar;

    2. select files to combine, as well as edit the list of selected files;

    3. combine all sheets of the selected files into one workbook;

    4. to combine into the workbook only non-empty sheets of the selected files;

    5. Collect sheets with a given name into the summary book (you can use a mask using special match symbols);

    6. to collect in one book sheets of selected files with a certain number (index), or a range of numbers;

    7. collect sheets with a certain value in a given range of cells;

    8. set additional parameters for merging, such as naming sheets of the files to be merged and deleting from the workbook in which the data is merged, of its own sheets that were originally in this workbook.

    If necessary, the names of sheets of the generated workbook can be quickly changed using the add-in for automatic renaming of sheets.

    Combine value ranges from different sheets of different workbooks on a separate sheet

    It is also possible to automatically iterate through all the workbooks specified in the dialog box, select only the necessary sheets in them, copy the user-defined value ranges and paste them on a separate worksheet of the active workbook. At the same time, you can choose the way of placing data on the sheet with totals; the ranges of values \u200b\u200bcan be located one after another both horizontally and vertically. So you can



    We recommend reading