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 commands of the application 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".
It is necessary
Tabular editor Microsoft Office Excel 2007 or 2010
Instructions
Step 1
If you need to combine into one not a very large number of sheets, it is not difficult to do it 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.
Step 2
Go to the sheet, the data of which you want to add to the pivot, and click the bottom right cell with the 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.
Step 3
Return to the summary sheet and paste the copied - press the "hot keys" Ctrl + V. Repeat the operations of positioning, copying and pasting the required number of times, 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.
Step 4
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.
Step 5
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, by clicking the mouse, indicate 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.
Step 6
Press the "New" button and between the first and last lines of code in the window that opens, enter, for example, the following set of commands: 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 Close the macro editor.
Step 7
Click on the created button, and the macro assigned to it will create a new sheet, in which it will merge the contents of all the others, linking them vertically.
