How to Excel data from multiple sheets to transfer one

This files most often work with tabular data in the office or at home you have a spreadsheet program Microsoft Office Excel. Each file of this application contains a single document, divided into separate sheets of a spreadsheet. Alas, among the teams of the apps can't auto-merge multiple sheets of a document in one. However, this is not so rare, and to solve it you have to either "manually" or using scripting "macros".

You will need

• The table editor Microsoft Office Excel 2007 or 2010.

Instruction

1. If you bring in one you need not a very large number of sheets, it is easy to make a simple combination of the operations copy and paste. Select the sheet that will bring together all of the table combined. If it already contains data, set the input cursor to the first cell for the added field in the cell of the first row far right column when adding data horizontally or in a cell of the first column, below the last line, when you add vertically.
2. Navigate to the sheet whose data you want to add in the summary, and click the bottom right cell with data. Press Ctrl + Shift + Home to select the entire table. Please note that it is necessary to select only cells with data and not all the contents of the sheet, otherwise when you paste, Excel will issue an error message. Place the copied area to the clipboard - press Ctrl + C.
3. Go back to the summary sheet and paste the copied - press the hot keys Ctrl + V. Repeat the operation, positioning, copy-and-paste required number times, if you want to combine more than two sheets. After the procedure, the copied sheets can be delete - click their shortcuts, right-click, choose Delete and click Yes in the confirmation dialog.
4. If you combine the sheets quite a lot, will have to use the script, ie to place in the page button and bind it to the corresponding macro. For this we need to use the Developer tab. If it is not displayed in the menu, click the free space on any tab, right-click and select "customize the ribbon". In the list of "Main tabs" check the box labelled "Developer" and click OK.
5. On the Developer tab open the drop-down list "Insert" command group "controls" and select the first item click. Then click specify the location in the table where you want to place the button and the screen will appear the dialog box "Assign macro".
6. Click "Create" and between the first and last lines of code in the opened window enter, for example, this command set:s_ = Sheets.CountSheets.Add After:=Sheets(s_)For i = 1 To s_ r_ = Sheets(i).Cells.SpecialCells(xlLastCell).Row Sheets(i).Range("A1", Sheets(i).Cells.SpecialCells(xlLastCell)).Copy Sheets(s_ + 1).Range("a" & n_ + 1) n_ = n_ + r_NextЗакройте macro editor.
7. Click on the created button and assigned the macro creates a new worksheet, which will combine the contents of all the others, clasping them vertically.