Shown as follows: Figure 2 2013 Data worksheet Step 3 Click the Total Sales worksheet tab, and input row and column labels. The Consolidate dialogue box will open and show as below: Figure 4 The Consolidate dialogue box Then do the following: Figure 5 Add the 2012 Data cells area Step 6 Click Add.Shown as follows: Figure 3 Total Sales worksheet Step 4 Choose the Total Sales worksheet as the target worksheet. Then the range will be added in the All References box. Add the range of B3: D9 in 2013 Data in the All References box.When you want to consolidate data by position, make sure that each range of data has the same layout.That means each range of data to be consolidated must be in the corresponding position on each separate worksheet.If you prefer to not use the Consolidate tool, you can easily create a macro that will do the consolidation for you—provided the structure of each worksheet is identical. The following macro steps through all the worksheets and combines the data to a new worksheet it adds at the beginning of the workbook. Fortunately, Excel includes a feature that allows you to do this very process—the Consolidate tool. Select ' copy cells selected in the new sheet on last line Selection. This tip (3005) applies to Microsoft Excel 97, 2000, 2002, and 2003. Name = "Combined"' copy headings assuming they are the same on all Worksheets Worksheets(2). The Consolidate tool allows you to combine worksheets where data is defined by position or by category. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Condensing Multiple Worksheets Into One.
We want to consolidate data in the first two worksheets and put the result in the Total Sales worksheet.By category means that you want to combine data from tables in which the data may not use a consistent structure. Specify in the box the first range you want to consolidate. What I'm trying to do is: I have a workbook which has 8 worksheets. I want to select some columns( A, G, L T) from each worksheetand copy all that data in a new worksheet in a different workbook in column A, G, L, T. You use this type of consolidation if the columns in the data tables are in different orders. If you are consolidating by position, then the reference should not contain any column labels; if by category, then you should. For a version of this tip written specifically for later versions.............." I had 67 sheets but couldn't figure out why only a fraction were being combined. Only one thing I'd do to make this perfect is to keep the first sheet and place the combined data into another sheet, but I can simply copy that sheet back in from a copy of the original file. I made some additions to the codes and added the ability to take the subtotal of the desired column: It's video:https:// If using a later version (Excel 2007 or later), this tip may not work for you. Since I'm using Excel 2016 which has a larger record limit, I changed it to "A1048576" and that did the trick I looked EVERYWHERE for a simple answer to my problem, to simply combine the tables in multiple worksheets (over 130 sheets) within a single workbook into a new sheet within the same workbook. The macro worked best for me, as I didn't want a sum, avg, etc., of my data, just copying the data while keeping my original into. Sub Combine() Dim J As Integer On Error Resume Next Sheets(1). Range("A1")' work through sheets Dim J As Long For J = 2 To Worksheets. The operation steps are as follows: Step 1 Click the 2012 Data worksheet tab, and input the data.Shown as follows: Figure 1 2012 Data worksheet Step 2 Click the 2013 Data worksheet tab, and input the data.In the workbook whose worksheets you want to consolidate, choose Data | Consolidate. (See Figure 1.) There are many controls in the dialog box, but the primary thing you need to worry about is specifying the ranges to consolidate. When you specify the range reference, you click Add, and the reference appears in the All References list. Range("A1") ' work through sheets For J = 2 To Sheets. You continue to define reference ranges until they are all complete.