Plus, discover how to create simple formulas that connect different worksheets, make super-efficient 3D formulas to calculate summary totals from multiple worksheets, and use Pivot Tables to summarize data from multiple workbooks.One of the most sought after query from the millions of Excel users around the world is: There are a couple of ways you can do this, using VBA or complex formulas but the learning curve is steep and out of reach for most Excel users.For example, if your sheet names are "East" and "West", change this line of code: In the code, you can also change the location where the pivot table will be added.In the sample file, the Table Destination is set for the active sheet, in range A1.
Then, use the result as the pivot table's source data.The file I’m working with, which you can download here or at the end of this post, is for a fake used car dealership that sells the Honda Accord, Honda Civic, Toyota Corolla, Toyota Matrix and Toyota Camry.There are 30 employees and three separate worksheets (one for January, February and March), each containing the total cars sold that month per employee, per car. The best way to learn is to practice yourself, so click the link below to download the Excel 2010 workbook used to show the methods described in this post. You can read more about MS Queries here: Instead of manually setting up a union query, you can use the code in a sample file from Excel MVPs, Kirill Lapin (KL), with amendments by Hector Miguel Orozco Diaz.Before you use the sample code, replace the sample sheet names with the sheet names in your workbook.Luckily with Power Query this consolidation task can be done in a couple of minutes! I show you how below…DOWNLOAD EXCEL WORKBOOKNow you can Refresh the Pivot Table and the new imported data will be reflected Next month all you have to do is drop in the new month’s workbook into the 2016 Sales Folder and Refresh the Query & the Pivot Table to see the results!To see how to create a pivot table from data on different sheets, watch this short video. If you can't combine your data on a single worksheet, another solution is to create named ranges in an Excel file, and use Microsoft Query (MS Query) to combine the data.Sometimes multiple worksheets or workbooks are the only solution to storing large amounts of data.But how do you analyze data that's spread out that way?Put additional variable declaration Dim wb Master As Workbook, wb Temp As Workbook Dim ws Master As Worksheet, ws Temp As Worksheet File Path = "\AM01PCIFS01.global.root\Home Dirs$\yameen.sarwar\Desktop\Test\II1\" My Files = "\AM01PCIFS01.global.root\Home Dirs$\yameen.sarwar\Desktop\Test\II1\*.xlsx" My File = Dir(My Files) With Application . Display Alerts = False End With '~~ Open the file and at the same time, set your variable Set wb Temp = Workbooks. Open(Filename:=File Path & My File, Read Only:=True) Set ws Temp = wb Temp.