Create a Pivot Table from multiple worksheets in the same workbook

{133 Comments}

Assume a workbook with two worksheets - Jan and Feb.  In both the worksheets, the following exist

1. Same number of columns on both worksheets; and
2. Same order of columns on both worksheets; and
3. Same spellings of headings on both worksheets

As you can observe in this workbook, all three aspects mentioned above are in place.  One may want to perform any of the following tasks:

1. Create one pivot table from both worksheets; and/or
2. Consolidate data from both worksheets (one below the other)

To curtail the workbook size, I have deleted rows of data.  In the actual workbook, there were 65,000 rows of data on each worksheet.

While one simple way would be to copy and paste data from both worksheets into one and then create a pivot table, the following shortcomings exist with this method

1. Copying and pasting data from multiple worksheets into one is a manual process; and
2. Since the pivot table will be based on the manually created sheet (by copying and pasting), changes in the two original sheets will not update the pivot table on refreshing unless the same changes are carried out in the consolidated sheet (created by copying and pasting above) as well.  So this leads to duplication of effort.

Both problems outlined above can be resolved by using MS Query.  The technique mentioned below will work as long as the three conditions mentioned above are satisfied.  Furthermore, since MS Query will only recognize named ranged with rows up to 65,536, the number of rows of data in each of the worksheets should not exceed 65,536.  The combined rows in both worksheets could be any number.

Depending upon the version of MS Excel which you are using, there could be two ways to solve this problem.

Solution for MS Excel 2010 and higher versions

If you are using the Power Query add-in, then a few simple steps (no formulas at all) can solve this problem.  The result will be dynamic and refreshable (just as in a Pivot Table).  In Power Query, there is a feature to append data from multiple worksheets into one running range.  Once this is done, the running range can either be transferred to an MS Excel worksheet (if the number or rows are up to 1 million) from where a Pivot Table can be created OR the running range can be loaded to the Data Model (Excel 2013) [the Data Model will be able to accommodate more than 1 million rows depending upon the hardware and software configuration of the machine] from where a Pivot Table can be created.

Solution for all versions of MS Excel

You may watch a short video of my solution here

The steps for creating a pivot table from multiple worksheets (both in the same file) are:

1. Suppose the workbook with the two worksheets (Jan and Feb) is named Monthly data.xls and is saved on the desktop.
2. Select the data on the Jan sheet (including the first row as the header row - on the Jan sheet, it will be A1:S4.  Ensure that the header row has some distinctive formatting such as Bold or some colour) and press Ctrl+F3 > New.  In the Name box, type Dummy and click on OK > Close.
3. To cross check that the name assigned above has indeed been assigned correctly, select the data range once again and in the Name box (left of the formula bar), Dummy should appear.
4. Select A1:S4 of the Jan sheet again and press Ctrl+T to convert this range into a Table.  Ensure that the "My Table has headers" box is checked.
5. Repeat steps 2 - 4 for the Feb sheet as well.  In step 3, just change the name to Dummy1
6. Save the workbook as Consolidated.xls (save as Excel workbook if the combined rows in Dummy and Dummy1 exceed 65,536 rows) and close it
7. Open a new workbook (Ctrl+N) and go to Data > From Other Sources > From Microsoft Query
8. Under Databases, select Excel files > OK
9. In the Directories dialog box, navigate to the folder on the desktop where the Monthly Data.xls file is saved.  So for me, it is saved under C:\Users\Ashish\Desktop\ and double click on the folder where Monthly Data.xls file is saved
10. In the left hand side window, select the Monthly Data file and click on OK
11. With Dummy selected, click on the > symbol to bring over all columns of this named range to the right hand side box
12. Select Dummy1 and click on the > symbol to bring over all columns of this named range to the right hand side box
13. Click on Next and on the message box which appears, click on OK
14. Click on the SQL button and delete all content from the SQL Statement box
15. Type this SQL statement in the box there

Select * from dummy
union all
Select * from dummy1

16. Click on OK and on the message box which appears, click on OK
17. Under File, select the last option - Return Data to Microsoft Excel
18. At this stage, if you wish to get data from Dummy and Dummy1, one below the other, then select Table.  If you directly want a pivot table, select the second option button - Pivot Table.  Select any cell where you would like to the result to appear, say cell A1.
19. Click on OK.  A counter will run at the bottom left hand side with the title of Reading Data
20. If you had selected pivot table in step 18 above, then the pivot table grid/layout will appear.
21. You may now drag fields to create a pivot table
22. Close the workbook

Update Table/Pivot Table for addition/editing in existing named ranges

To see the effect of a dynamic Table/Pivot Table, edit data in Jan and/or Feb sheets of Monthly data.xls.  Save and close the file.  Open Consolidated.xls and refresh the Table/Pivot Table (Right click and Refresh).  At the bottom right had side the counter will run again and (step 19 above) and once it has read all data, the Table/Pivot Table will update.

Furthermore, since the two named ranges (Dummy and Dummy1) have been converted into Tables, even if you add data by rows (with no row being left entirely blank), when you right click to Refresh the Table/Pivot Table, data of new rows will appear.

Update Table/Pivot Table for addition of new worksheets

Assume you now add a new worksheet, titled March in Monthly data.xls.  To include this sheet in the pivot Table, follow the under mentioned steps:

1. Open Monthly data.xls

2. Follow steps 2 - 4 mentioned above for the March sheet.  In step 3, just change the name to Dummy2

3. Save and close the workbook

4. Open Consolidated.xls

5. Select any cell in the pivot table and under Pivot Table Tools (red button in the MS Excel title bar) > Options, click on the small drop down arrow under Refresh and select Connection Properties.  If you selected Table (instead of Pivot Table) in step 18 above, click on any cell in the Table and under Table Tools (yellow button in the MS Excel title bar) > Options, click on the small drop down arrow under Refresh and select Connection Properties.

6. Click on the Definition tab and under Command Text, add the following at the end of the SQL query:

union all
Select * from dummy2

7. When you click on OK, the counter will run again and the pivot should reflect data from March sheet.

Update Table/Pivot Table when files are mailed to someone else

If you now mail both files - Monthly Data.xls and Consolidated.xls to someone else, that other person will not be able to refresh the pivot table because the path specified in step 9 above would be different for that other person.  To reestablish the path, follow the under mentioned steps:

1. Open Consolidated.xls

2. Select any cell in the pivot table and under Pivot Table Tools (red button in the MS Excel title bar) > Options, click on the small drop down arrow under Refresh and select Connection Properties.  If you selected Table (instead of Pivot Table) in step 18 above, click on any cell in the Table and under Table Tools (yellow button in the MS Excel title bar) > Options, click on the small drop down arrow under Refresh and select Connection Properties.

3. Click on the Definition tab and under Connection String, edit the following:

a. From DBQ= till the next ;, change the file path to the new path where the mail recipient has saved the file.  So, on my system, between DBQ= and the next ;, the path is C:\Users\Ashish\Desktop\Jan.xls.  This will change to C:\Users\John\Desktop\Data\Jan.xls

b. From DefaultDir= till next ;, change the folder path to the new path where the mail recipient has saved the file.  So, on my system, between DefaultDir= and the next ;, the path is C:\Users\Ashish\Desktop\.  This will change to C:\Users\John\Desktop\Data\

RSS 133 Comments…

 Share your views
  1. I'm in the middle of using this to setup a pilot test. One question - could it be setup so that the consolidation page on the data is within the same workbook as the data entry sheets? I also notice that one of my columns being imported changes from a decimal value to a date format? Have you heard of this happening before? Just curious?

    • Hi,

      No major changes at all. In step 7, do not open a new workbook. In Consolidated.xls., open another worksheet and all other steps should remain the same. I am not sure of why the conversion is happening. I have not experienced it.

  2. Hi Ashish,
    I think I'm doing something wrong. I've created my spreadsheet with the 9 tabs I wish to consolodate based on the instructions from your link. When I'm following the steps to name the tables and consolodate them, in step 11, now I cannot find the names I created for the tables? I've tried to troubleshoot this, but I can't figure out why it's not picking up the tables. If I'm using Office 2007 is there any additional steps I need to take? Would you be willing to take a look at the file and advise if I'm missing something? I'm at my wits end and would appreciate a fresh set of eyes (Brain cells) if you're able to. Let me know and I'll forward you what I've created.

    • Hi,

      Please upload your workbook to SkyDrive and share the link of the workbook here.

      • No problem, and thank you for taking a look at it. Can you advise how to access SkyDrive (Never heard of it - I'm a little new to some of this, so thanks for your patience and understanding! :) )

        Once it's uploaded, I'll email you the link as requested and a description of what I'm trying to accomplish.

  3. Hi,
    I tried to post this to the conversation on consolidating data on multiple tabs to one tab, but received a server error indicating it did not save my post. Attempting to post this way instead.

    It's been posted to SkyDrive at (Greg Tunney's SkyDrive/Public), file name is : Prototype-2013 Timesheet v5.1.

    The 'template' tab is a timesheet entry template tab I'd use to create new individual sheets for users. I've entered 3 users as a test (Greg, Sandra,Val) and followed your steps to setup the tables and use CTRL F3 to name them. Note-When this didn't work, I attempted the table names again, but changed the scope in an attempt to troubleshoot it. The issue I'm having is when I attempt to consolidate the 3 sheets onto an empty sheet, I cannot find the 3 table names I've assigned (GregTime, SandraTime, ValTime). Not sure if formating or any functionality I've setup is causing the issue. Would appreciate any feedback you can provide on what I need to adjust. Thank you. Greg

    • Hi,

      I do not understand what you want to do. Please get rid of all errors on the worksheet and explain what you want to do.

    • Greg did you ever get this to work? I am having the exact same issue, I need the consolidated table in the same workbook as the data entry not a separate file. Fingers crossed that you found a solution you can share!

  4. This is fantastic and it worked perfectly for me. Thanks for these clear directions!

  5. Hi!

    I want to start off by saying this is a great guide which helped me a lot. However, I have encountered some problems when trying to import more than 3 sheets from MS Query. Right now I have 5 sheets with 65,536 rows of data in each. When I enter

    Select * from Sheet1
    union all
    Select * from Sheet2
    union all
    Select * from Sheet3

    in the SQL Query everything works fine. However, if I add another line stating

    union all
    Select * from Sheet4

    I get two error messeges. First it says "Internal OLE Automation error" and afterwards "Problems obtaining data". I can combine any 3 combinations of my 5 sheets and get it to work, so the problem is not in any of my sheets. But when I try to import more than 3 sheets I get this error.

    Any idea as to what might cause this?

    Thank you in advance.

    • Hi,

      Please upload your workbook (with the five worksheets) to SkyDrive and share the link of the uploaded file here.

      • Thanks for a quick reply.

        I wish I could do that, but I am afraid that due to the confidentiality of my data I am not going to be able to share it. I understand that this will make it harder for you to help me, but if you have an spontaneous idea as to what might cause this maybe you can share it.

        If not, I will keep on trying to fix it on my own or maybe look for an alternative approach. Thank you for you time though.

        • Hi,

          There is no limit of being able to Pivot only from three worksheets. I have created a Pivot Table from 4-5 worksheets as well. Furthermore, I cannot pinpoint any error in your query.

          You can delete most of your data from the five worksheets (leave only 10 rows of data on each of the five worksheets) and then share the file. That is all the help I can offer.

          • I understand. When I use 3 or more sheets it does load the data up untill about 192,500, thats when I get the error messege. I feel like the wrong is somewhere there.

            I very much wish I could share any data but I am afraid I cannot. I will keep trying with less rows and 5 sheets, and see if it works then.

          • Hi,

            Try this

            1. Create a Pivot Table from the first three worksheets only
            2. Select any cell in the pivot table and under Pivot Table Tools (red button in the MS Excel title bar) > Options, click on the small drop down arrow under Refresh and select Connection Properties
            3. Click on the Definition tab and under Command Text, add the following at the end of the SQL query

            union all
            Select * from sheet4

            4. When you click on OK, the counter will run again and the pivot should reflect data from sheet4

            Does this work? If it does, then repeat the steps above for sheet5 as well.

  6. I did what you described several times but didn't get it to work. However, I just now managed to get the whole thing to work but I am not sure how.

    I basically just re-did the whole guide with new names on everything and new filenames. I am wondering what might have caused the issue. Could it be that I had my data-file in xls format and my pivot table file (consolidated) in xlsx format?
    I am going to explore this further in order to realize what exactly I did wrong. Thanks again for an excellent guide and thanks for your time! :)

    • Hi,

      Great to hear that it is working fine. As mentioned, I have several times in the past consolidated/Pivoted data from multiple worksheets without ever facing the problem which you mentioned. Not sure of why you faced this problem.

      You are most welcome. Glad to help.

  7. Very useful post. Solved my requirement within no time.
    Great work.

  8. Hi, when I am making a single pivot out of multiple worksheets, the column headers are not gettin retained. Please help.

    • Hi,

      Could you describe your problem in greater detail.

      • suppose both my data ranges has 4 columns. when i am making the final pivot out of the 2 ranges...through "Multiple Consolidation Ranges" option on Pivot Table wizard, the First Column is automatically becoming my "Row Label" and the rest 3 Columns are getting clubbed under a "Column Label". where the pivot is giving me the sum values under 3 categories (under "Column Label")...I am not being able to use these categories as separate variables in my pivot.

        • Hi,

          "Multiple Consolidation ranges" is not used for creating a Pivot Table from multiple worksheets. To do so, please follow the steps which I have mentioned in this link - by writing an SQL query.

  9. Thank you very much..
    Super way of explaining....

  10. Hi,

    Can u pls send how to load data from different excel files into one another new excel file.

    My project:
    I have different files, but the content in the files is same format. I want to pick only two fields from all the files and load the data into a new excel. Pls suggest.

    Thank you.

    • Hi,

      You may try my solution at this link. To create a Table (instead of a Pivot Table), select Table in step 13. Also, in step 10, click on the + symbol and then select the two columns which you want to consolidate.

  11. Hello,

    I am having the same problem a prior person on this string had....my pivot will only pull the data from the last 3 worksheets in my workbook. I have 7 worksheets that I would like my pivot to pull from. I made sure that I have consistent formatting across all worksheets and I also tried to address the issue through the connection properties steps that you outlined, but no success! - Can you help or provide further guidance?

    Thanks!!

    • Hi,

      How many rows of data per worksheet do you have? Also, as mentioned by Christoffer (whose Comment you are referring to), he just redid the whole thing and it worked fine. So I would suggest that you just start from scratch.

      Also, please be specific about the error which you are facing.

      Lastly, even if on retrying, you do not get your expected result, then upload your file to SkyDrive and share the link of the uploaded file here.

  12. I get a final error message saying "[Microsoft][ODBC Excel Driver] System resource exceeded." My first tab has 13,021 rows of data and the 2nd tab has 12,857 rows of data. Thoughts?

    • Hi,

      Looks like the RAM of your machine is used by some other heavy application. Two worksheets of 13,000 rows is nothing. I very often create a Pivot table from three worksheets with 65,000 rows on each worksheet on a 2 GB RAM machine.

      You may try the following:

      1. Close all applications and then try this method. If it works, then it definitely because of RAM being used by other applications.
      2. If 1 does not work, then try the same process on another machine.

      Also, how many columns do you have on each worksheet?

  13. Hi Ashish thank your very very very much,.... you made my day

  14. Dear Sir,
    I have made a pivot table from merging multiple worksheet into one pivot data source through MS Query, but sir when I mailed to someone the workbook then there need to change User name from Connection string, I want to know that there is way to change connection string automatically when user save the sheet into there system

    • Hi,

      I don' think so. If you have only a few worksheets to create a Pivot Table from and if you are using Excel 2010 or higher versions, then you can use the free Power Query Excel add-in from Microsoft to dynamically combine data from multiple worksheets into one. One can then create a Pivot Table from this dynamically created consolidated sheet.

      A Pivot Table so created will not require any adjustment one the file goes to someone else by mail.

  15. Great, great, great and useful article. Thank you for the super easy to follow steps. Helped me a great deal

  16. Can a pivot table be created from 2 worksheets that do not have the same column names or row name and can not normally be combined for that reason? I have a worksheet containing transactional information on loans and the other worksheet is loan characteristic information. I need information from both places to create pivot tables that contain transactional information and include loan characteristics with both worksheets only have the loan number in common.

  17. Fantastic Ashish... like the way you have explained step by step. Meanwhile, I have a similar question - I use Excel 2010, and need to create a pivot table from two different worksheet having different column names and column nos.

    I believe currently this is possible from Excel 2013, but wanna take your workaround options in 2010. Please advice.

    • Thank you. I am glad that you liked my post. This will purely depend upon what you want done and how your data is arranged. Upload your file to SkyDrive and share the link of the uploaded file here. Please explain your requirement and desired output in detail.

  18. Hi,

    Status of tasks (initiated, pending, closed, etc) are tracked date wise and each sheet holds datewise task status for a particular month. I need a pivot that will display the sum of tasks by status monthwise. Can you advise how to get the monthwise summary.

    Sheet1 (Jan)
    01-JAN-2014 Task 1 (Closed)
    02-JAN-2014 Task 2 (Pending)
    Sheet 2 (Feb)
    01-FEB-2014 Task 1 (Pending)
    02-FEB-2014 Task 2 (Pending)

    Need a pivot that will display,

    January
    > Pending = 1
    > Closed = 1
    February
    > Pending = 2

    Please advise.

    • Hi,

      Have you followed the steps which I have outlined in my original Blog post? If you follow those steps, you will exactly get what you want. Once you create the Pivot Table, you will just have to Group the Dates by Months.

      Hope this helps.

  19. Yes, I got the rest but I wasnt aware of group by the dates. I will try and let you know.

  20. Got it. thanks for the hint.

  21. Hi Ashish,

    really superhelpful. Thanks! It worked for my 20 sheets as well.. Beside some minor formatting differences (numbers to text) there wasn't any real problem.. until now..
    All my sheets are identical in formatting, size and data identification (columns used to set identification/mapping to values), values are of course different.
    It looks something like this:

    First line (Columns: Name, XY, Jan-Dec)
    Second line (Columns: John, YES, 1...)
    Third line (Columns: Jack, NO, 2...)
    ...

    I added another column into my data sheets (all of them of course) and refreshed the table I had linked to those sheets. Then I experienced this weird thing: Some on my mapping/identification cells were simply blank, even though they contain some value in the data sheets. And guess what, this happened only for some of my data sheets (3 out of 20). /again, all my data sheets are the same in everything but values/ Result:

    Data (Columns: John, YES, 1...)
    Mapping (Columns: John, , 1...)

    I tried everything from the beginning, changed some formatting, tried manual links to data sheets (deleted after table refreshing), tried playing with some setting in the query sql code..
    no change, some cells are simply empty.

    Could you please help me with any brainstorming, ideas, whatever, as this little mistake is making all my table completely useless.. and I'm really frustrated already..

    Thanks a lot for any advise!

    Maros

    • You are welcome. If you are adding a new column, then please ensure the following:

      1. Add the column in all worksheets at the same position; and
      2. The spelling of the column heading should be the same; and

      My guess is that while you have added the column to all worksheets, you have not expanded the Named ranges. Ensure that the Named ranges include the new column which you added.

      • Hey Ashish,

        thanks for reply.

        I added column with the same name to the same position into all my data sheets, and I double checked name ranges. All works fine except mapping from 1 column from 3/20 sheets.. Btw, those problematic columns are different from ones I added last.

        (working excellently -> added new column -> some old columns not mapping -> frustration)

        It's really annoying, especially, when I want to use table as a building brick for another analysis..

        Seriously, I have no idea what to do with it..

        Please, if you have any idea, I would really appreciate your help..

        Cheers!

        Maros

  22. Hi Ashish,

    Your solution worked perfectly for me on my desktop
    But my actual file is in sharepoint, not sure how to coonect it to networkdrive?

  23. Can i place Sheets vertically or is there any software

  24. Great steps! I was able to follow without any problems.

    However, how do I add new columns to the final consolidated table? I know how to adds new rows, change and edit data, but if I wanted to add a new column heading how will I do that?

    Thanks!

    • Hi,

      Thank you for the feedback. Once you have created the Pivot Table and now want to add additional columns to the source data sheets (and obviously have the additional columns appear in the Pivot Table Field List as well), try this

      1. Add column heading(s) in source data sheet1 and copy the same heading(s) with in the same cell(s) of the other worksheets as well
      2. Add entries under these columns
      3. Go to Formulas > Name Manager and select the name of the first table > Edit. Extent the range to include the additional column(s) added
      4. Repeat step 3 for all Named ranges
      5. Refresh the Pivot Table. The new columns should now appear in the Pivot Table Field List

      I have tried and tested this on a dataset and it works fine.

      Hope this helps.

  25. Thanks Ashish, this really helped!!

  26. Hi,
    I have been able to get this working with out issue which is great, thanks for posting this as it has been very usefull.
    Is there a way that I can easly "save as" so that i can re use the master version and save old data of a previous month? The one I have is using 10 sheets and covers a month of data that is populated into a Pivot chart, I want to open it again in 12 mths time and still be able to manipulate the Pivot table/chart based on the data from 12 mths ago.
    I have tried changing the connection string but get errors after a couple of saves and I'm trying to make it easy for the end user that will be updated the data and saving the file to start a new month. any thoughts will be great. cheers.
    Mark

    • You are welcome. I am glad you could make use of this technique. Since Save as would either cause a change in the directory or the file name, the connection string would have to be changed. You may want to explore the Power Query option which I have shared in the same article. Via Power Query, you will first be able to append data from all sheets into the Power Query window and we will then be able to create a Pivot Table from it. Since there are no external connections, you will not need to change the Connection string.

      Hope this helps.

  27. Dear Ashish,
    I found your blog very clear and helpful to me, I am trying to do a pivot with data from 12 sheets with the following info: Category (column C of the original file) and Sub-category (column D) in rows and Amount (column A) in column.
    With the first sheet everything was ok.
    When I add "union all Select * from dummy2" it shows the right amount but not Category nor Sub-category...but the column B of the original file.
    Do you have any clue of what's wrong?
    Thanks in advance.

  28. Hi Ashish,

    Excellent Article. I have few questions.

    1) Can conditions be added to the query? I mean instead of saying bring me all the data (union all), can we write a query saying
    Select * from dummy1
    union all
    Select * from dummy2
    where dummy1.empID = dummy2.empID

    2) For the steps mentioned in you article to work, 3 conditions have to be satisfied (column names, position & no.of columns have to be same)
    Even if one of them doesnt satisfy, the above steps dont work. What is the workaround for it.
    Because I have three named ranges with different column names but I want to combine all three to create a pivot.

    Thanks for all your help.

    • Hi,

      I am not quite clear about your first requirement. Once the Union All query has been run and data gets appended to create one table (all Emp ID's appear in one column), then what would the sense of dummy1.empID = dummy2.empID be?

      Please explain your question in detail and if possible also share the link from where I can download your workbook.

  29. Hi,

    Is their any macro for multiple files & multiple sheets data pull in Pivot table

  30. I have this macro but pivot field mention in code i want dynamic any type of data i want pull pivot table please tell me
    Option Explicit

    Private Declare PtrSafe Function SetCurrentDirectoryA Lib "kernel32" (ByVal lpPathName As String) As Long

    '---------------------------------------------------------------------------------------
    ' Author: Rob Bovey
    '---------------------------------------------------------------------------------------
    Sub ChDirNet(Path As String)
    Dim Result As Long
    Result = SetCurrentDirectoryA(Path)
    If Result = 0 Then Err.Raise vbObjectError + 1, "Error changing to new path."
    End Sub

    '---------------------------------------------------------------------------------------
    ' Procedure : MergeFiles
    ' Author : KL
    ' Date : 22/08/2010
    ' Purpose : Demonstration (http://www.planetaexcel.ru/forum.php?thread_id=18518)
    ' Comments : Special thanks to
    ' Debra Dalgleish for helping to fix ODBC driver issue
    ' Hector Miguel Orozco Diaz for the "DeleteConnections_12" idea
    '---------------------------------------------------------------------------------------
    '
    Sub MergeFiles()
    Dim PT As PivotTable
    Dim PC As PivotCache
    Dim arrFiles As Variant
    Dim strSheet As String
    Dim strPath As String
    Dim strSQL As String
    Dim strCon As String
    Dim rng As Range
    Dim i As Long
    Dim shtArray As Variant
    Dim shtCount As Long
    'List of sheet names
    shtArray = Array("Travel_Query", "Travel_Confirmation", "Salary_Query", "Salary_Confirmation", "Absence")

    strPath = CurDir
    ChDirNet ThisWorkbook.Path

    arrFiles = Application.GetOpenFilename("Excel Workbooks (*.xls*), *.xls*", , , , True)
    strSheet = "Sheet1"

    If Not IsArray(arrFiles) Then Exit Sub

    Application.ScreenUpdating = False

    If Val(Application.Version) > 11 Then DeleteConnections_12

    For shtCount = LBound(shtArray) To UBound(shtArray)
    strSheet = shtArray(shtCount)
    Set rng = ThisWorkbook.Worksheets(strSheet).Cells
    rng.Clear
    For i = 1 To UBound(arrFiles)
    If strSQL = "" Then
    strSQL = "SELECT * FROM [" & strSheet & "$]"
    Else
    strSQL = strSQL & " UNION ALL SELECT * FROM `" & arrFiles(i) & "`.[" & strSheet & "$]"
    End If
    Next i
    strCon = _
    "ODBC;" & _
    "DSN=Excel Files;" & _
    "DBQ=" & arrFiles(1) & ";" & _
    "DefaultDir=" & "" & ";" & _
    "DriverId=790;" & _
    "MaxBufferSize=2048;" & _
    "PageTimeout=5"

    Set PC = ThisWorkbook.PivotCaches.Add(SourceType:=xlExternal)

    With PC
    .Connection = strCon
    .CommandType = xlCmdSql
    .CommandText = strSQL
    Set PT = .CreatePivotTable(TableDestination:=rng(6, 1))
    End With

    With PT
    With .PivotFields(1) 'Rep
    .Orientation = xlRowField
    .Position = 1
    End With
    .AddDataField .PivotFields(8), "Sales", xlSum 'Total
    With .PivotFields(3) 'Region
    .Orientation = xlPageField
    .Position = 1
    End With
    With .PivotFields(2) 'Date
    .Orientation = xlColumnField
    .Position = 1
    .DataRange.Cells(1).Group _
    Start:=True, _
    End:=True, _
    Periods:=Array(False, False, False, False, True, False, True)
    End With
    End With

    'Clean up
    Set PT = Nothing
    Set PC = Nothing
    Next shtCount

    ChDirNet strPath
    Application.ScreenUpdating = True
    End Sub

    Private Sub DeleteConnections_12()
    ' This line won't work and wouldn't be necessary
    ' in the versions older than 2007
    '*****************************************************************************
    On Error Resume Next: ThisWorkbook.Connections(1).Delete: On Error GoTo 0
    '*****************************************************************************
    End Sub

  31. Hi Ashish:

    One quick question. in step 6 you mention saving the file containing the tabs jan and feb as consolidated. I think it needs to be the new sheet containing the imported data that needs to be saved as consolidated. Step 6 needs to happen after i open a new file in step 7 and at the end of step 22. please comment.

    Thanks
    Akshay

  32. Hi Ashish,

    your guidance worked superb at consolidating expnese sheets.

    Many many thanks,

    Zoltan

  33. Hi Ashish,

    Thank you so much, this post saved me from all the trouble of doing it manually.
    Awesome step by step help, thanks again!!

  34. I got this but I noticed that two of my columns being imported changes from a decimal value to a date format for data of 1 worksheet only for 2nd worksheet data it shows correct format (in decimal only). please help on this

    • Hi,

      This could be an Excel version specific problem. If you can upload the workbook to OneDrive and share the download link with me, then I will check whether this happens on my system as well.

      • Hi,
        I checked it on other systems still its happening.
        I have four worksheets with same no. of columns (say 8 columns) & same title in each sheet. when I combine all four sheets data in one new sheet, it works well but if i add new columns like column no. 9, 10 n so on...... formatting changes automatically for few of earlier columns. if i delete 1 newly added column, formatting changes randomly for few of earlier column. Is there any restriction for no. of columns? or am i doing it anything wrong. Thanks in advance for your help.

  35. Hi Ashish,

    I am using Excel 2010 and am attempting the union all via Microsoft query, I am able to get to the dialogue box an enter the SQL but after the "can't be graphically represented" prompt I get a "Syntax error in query. Incomplete query clause." message. It then kicks me back to the SQL, I can't move beyond this point. the tables I am selecting are linked to an external source already, is this causing the issue?

    hope you can help!

    thanks

    A

  36. Ashish,
    I am using MS Excel 2010; I got as far as selecting the folder that I created in MS Query; only 2 of the 3 tabs showed up Dummy1 & Dummy2, Dummy did not though it showed fine when I originally renamed it. Also when I tried to move the columns it said there were too many and wouldn't proceed farther. I have 160 columns, though only 100 rows. Is there a column limit? Great article and any help would be greatly appreciated.

  37. Great guide. Many thanks for that. Quick question: auto-refresh the pivot table via VBA. Is it possible?

    Paul

  38. Very helpful article and video. Good examples used, easy to understand, explained in layman's terms. Very valuable. Exactly what I needed. Thank you so much!

  39. Hi Ashish
    I watched your YouTube clip on creating a pivot table from multiple worksheets of a workbook

    Thanks you this was very good and easy to understand.

    I am having one issue that I can not resolve.

    When I return the data to excel it does not keep the data in a number format that the source data is in. Some of the data is changed to text format and some is shown as a date format.

    The number format in the original worksheet is unchanged.

    When it comes time to using the sum on the pivot table it does not work as the pivot thinks it is text or a date.

    Do you have any ideas please

  40. Hi Ashish
    I have tried this multiple times with no luck. Create all the named ranges, convert to tables and save the file.

    When I choose the file name of the current open file and the query wizard opens, the table names do not show up. I have all of the boxes checked on the options, they still do not show up.

    Now if I open a new file and choose the previous file as the data source, everything shows up fine and I can join the tables and insert the pivot, but the problem is that I need these all in one workbook..

    Any ideas here? I am running Office 2013 professional 32b on Win 7 64b. Not sure if that has anything to do with it or not? I would REALLY appreciate any help you can give, I have spent more than 2 weeks trying to figure this out.

    Thanks
    Marty

  41. Hi Ashish
    I was also able to test the same worksheet on Excel 2010 64b on a 64b win 7 machine and it does the same thing. If I create a blank workbook and use the main file as the data source, it has no issue and loads all the tables / columns into the query and can perform the join. I am really stumped here and need this to work so badly!
    Thanks
    Marty

    • I moved the pivot from the summary file into the main file and it shows up, but I can't get it to update if I change the quantities on the tabs?

      • Hi,

        I am at a loss. All I can now suggest is to move all ranges (from which you want to create the Pivot Table) to individual worksheets and then see if it works. Start from a clean slate. Remove all Tables and Named ranges and try afresh.

        • Ashish
          Thank you for looking at it, I am in such a mess now. I need this to work so badly! Weird that it works fine in an external file but not the same workbook. I have tried everything to no avail. It also works with power pivot but the other employees don't have the plus version.

  42. Hi, THANK YOU for this. Your video was a tremendous help to me today. One problem I am having is that I am trying to combine 6 tabs into a pivot table, but only 4 are showing when I get to Data-->From Other Sources-->From Microsoft Query. I was successful in some workbooks that had 2 tabs, so I'm wondering if there is a limit to the number of tabs that can be combined? Thank you.

  43. I watched your video on Consolidate multiple Worksheet into one Excel Pivot table. Impressive techniques. I have tried using this technique in past but was not able to build one single Pivot table with the field I wanted. However, if I create Pivot table from single worksheet then I can get the correct fields, which I wanted. I have five different sheets in single workbook. All have same headings and formatting but different number of rows. First column (Column A) contains Employee ID, Column "B" contains "Type of Leave" and Column "C" contains hours

    When I created Pivot table for Multiple sheets, I am getting only Total hours but not able to get Type of Leave and corresponding hours. I tried with same row numbers for all different sheets but doesn't get type of leave

    Link for file: https://onedrive.live.com/redir?resid=9609abbf5b972b9!110&authkey=!ANbdZ0pcQwpKu_o&ithint=file%2cxlsx

    How can I resolve this?

    Thanks for your help

    Chirag

  44. […] already created a pivot table and chart using Microsoft Query. I followed the steps from this blog: http://www.ashishmathur.com/create-a-pivot-table-from-multiple-worksheets-in-the-same-workbook/ Right now, I’m figuring out how I can add new data into pivot table. Is it possible to add […]

  45. Hello Ashish,

    Thank you for the tutorial, I've been using this every day and appreciate the info.

    My pivot table was working fine and now this is the error that I get when I try and refresh the pivot table:

    [Microsoft][ODBC Excel Driver] The Microsoft Access database engine could not find the object 'dummy.' Make sure the object exists and that you spell its name and the path name correctly. If 'dummy' is not a local object, check your network connection or contact the server administrator.

    There are a few things that I did that might have affected the file. I re-named my spreadsheet after using the query and I also e-mailed a copy of it to a colleague. Would this be causing the error?

    One thing that is different on my sheet from your tutorial is that I kept both the source data and pivot table in one file. in your tutorial you had a data.xlsx file and a consolidated.xlsx file. Is that an issue?

    Unfortunately I work with confidential data so I am unable to upload the file, is this a quick fix or should I start over again?

    • Hi,

      You are welcome. The error is being caused by renaming the workbook. Furthermore the mail receipient of the workbook has to re-establish the link to MS Query. Read the section titled "Update Table/Pivot Table when files are mailed to someone else" of this Blog article.

      Hope this helps.

  46. Hello Ashish,

    I am very impressed with your knowledge of Excel. I had been looking for a more efficient solution to my data tracker and I've learned so much from your tutorial. I attempted your solution here and I feel I still need to do more in order for it to do what I need. My data workbook has over 15 sheets - 1 sheet representing each month of the year, plus a Grand Total sheet and sheets with other data. I am tracking the number of hours of consultation per site location. I add up the hours by site location, per month, per quarter, and then add everything all together for a grand total of hours. Currently I keep the site locations on one column, then the hours, within each month's sheet. I enter site locations as they receive consultation so they are in the order by date. At the end of the month, I add each location's hours to the Grand Total sheet. I manually do this cell, by cell. Once a quarter passes, I do the same to tally the total hours for the quarter (ex. April, May, June). I know there is an easier and more efficient way to do this. Perhaps you can give suggestions? I appreciate any input. Thank you!

  47. Here is the link to an example of my workbook. I copy/pasted into this example so the cells are not adding up like in the original workbook.
    https://drive.google.com/a/ccyakima.org/file/d/0B7lQB-XDFwyYampPNVlMT3ZVSWM/view?usp=sharing

  48. Hi, I have tried this blog in both excel 2013 and 2010 and can't get it to work in either. In 2013 I get up to the SQL query where I see my tables but it keeps putting $ signs after the name of the table, so when I go to change the query to select * from etc it can't find the tables? In 2010 I create the tables and go to Microsoft query but it can't even find the tables at all? any help would be great, cheers, Vanessa

    • Hi,

      I cannot figure out why this should be happening. I've used this same technique in versions from Excel 2003 to Excel 2013 and it has always worked. If you can share your workbook (over OneDrive/Googls Docs), then I can download and check at my end.

  49. Thanks for this great guide.i used msexcel2013 and i managed to exceed the 6xxxx rows limit and have consolidated 14 excel files into one pivot table of 13columns and 970263rows without errors! In some source file the rows were like 290xxx and 170xxx.. Just takes time at 5-10mins everytime i update my pivot.

Leave a Comment

Your email address will not be published.

*