Create Excel Pivot Table from Multiple Sheets

Pivot Table from Multiple Sheets

A common pivot table question is “How can I create a pivot table from data that’s on separate sheets in my workbook?

Sometime people have a workbook set up with a separate sheet for each region, or for each salesperson.

Eventually, they want to pull all the data together, and create a summary report in a pivot table, from multiple sheets.

Multiple Consolidation Ranges

Excel has a feature (well hidden Excel 2007) that lets you do this, using Multiple Consolidation Ranges.

A pivot table created this way has limited features, and isn’t much use in summarizing Excel data.

I usually recommend that you move all the data onto one worksheet, if it will fit, or store it in a table in Access, then use that as the source for the pivot table.

Create a Union Query

Another solution is to create a Union query from the separate tables, and use that as the source data.

Create a Union Query in Microsoft Query
Create a Union Query in Microsoft Query

Normal Pivot Table

With this solution, you’ll end up with a normal pivot table, with none of the limitations. However, it’s a bit tedious to set up, especially if you have more than a couple of tables.

Automate the Union Query

Instead of setting this up manually, you can use the code in a sample file from Excel MVPs, Kirill Lapin (KL), with amendments by Héctor Miguel Orozco Diaz. (You might remember Héctor’s innovative Filter Pivot Table Source Data example, posted earlier this year.)

To adjust their sample code to work in your file, you’d replace the sheet names in the CreateConnection code.

To go to the CreateConnection code, right-click on the “Create Empty Table” button, and click Assign Macro, then click Edit.

Pivot Table from Multiple Sheets 02

You can also adjust the location where the pivot table will be added. This line is further down in the CreateConnection code.

Pivot Table from Multiple Sheets 03

After those small changes, save the code changes. Then go back to Excel, click the button on the worksheet, and a summary pivot table will be automatically created.

Download the Sample File

Thanks Kirill and H̩ctor, for making a complicated task easier. You can download their sample file from the Contextures website: PT0023 РPivot Table from Multiple Sheets

(Also, please check the update section below, for a newer version of the file)

UpdateDecember 2011

The solution described in this article was created as a conceptual prototype and targeted mainly advanced VBA users. The code has minimal error handling and compatibility checks.

Given the massive response from all kinds of users willing to adopt this solution in their own applications, we would like suggest a similar solution based on ADO.

Advantages:

  1. No need for temporary file generation
  2. The code is faster and less prone to errors

Disadvantages:

  1. No manual refresh of the PivotTable
  2. Need to rebuild connection from the scratch to update the cache with new data

Download the ADO Sample File

You can download the new ADO version of the file from the Contextures website: PT0024 – Pivot Table from Multiple Sheets – ADO version

Update — August 28, 2012

In the comments below, Kirill posted code that will automatically detect the sheet names. The blog formatting changed his minus sign to a long dash, and also deleted the Less Than Greater Than operator. Here is the correct code, with Kirill’s instructions:

In the code, replace this line:
' Sheets to consolidate
'*****************************************************************************
arrSheets = Array("310_BWATTS_P Pastujova", "310_BWATTS_Maria Sanchez")
'*****************************************************************************
with the following code:
' Sheets to consolidate
'*****************************************************************************
Dim ws As Worksheet
ReDim arrSheets(0)
For Each ws In ThisWorkbook.Worksheets
  If ws.Name <> ActiveSheet.Name Then
    arrSheets(UBound(arrSheets)) = ws.Name
    ReDim Preserve arrSheets(UBound(arrSheets) + 1)
  End If
Next ws
ReDim Preserve arrSheets(UBound(arrSheets) - 1)
'*****************************************************************************

________________

152 thoughts on “Create Excel Pivot Table from Multiple Sheets”

  1. Kirill and Hector
    Brilliant work… all my earlier attempts to use ADO/SQL to consolidate data from different sheets in an ActiveWorkbook always resulted in a memory leak (where a ghost instance of the file remained in the the VBE)

    Your technique of first creating a temp file, building the pivot and then changing the source to the active workbook solves this problems ….Simply brilliant. Also this technique does not need references to ADO Library

    The only small hitch is that SQL cannot be changed externally… Has to be done via code…but that OK…

    Thanks Debra for putting this file on your blog..

  2. […] Contextures Blog » Create a Pivot Table from Multiple Sheets (tags: excel pivot.tables tips howto) […]

  3. Good proposal. Thanks.

    But code will hang on this line:
    Set PT = .CreatePivotTable(TableDestination:=ActiveSheet.Range(“A1?))
    “Hang” means, the standard dialog for selecting a file or system DSN will be displayed.

    Any ideas why?

    I’m using WinXP, Excel 2003, German.

  4. Supplement:
    I’ve tried to to this in Excel 2007.
    And it worked for me!

    So what is the reason to have it not running under Excel 2003?
    I’d like to prepare a solution for both Excel versions.
    Thank you.

    best regards,
    hugo.

  5. I got macro to work great,

    Issue Im having now is, pivot has stopped refreshing data, as if cashe is full and needs to be emptied in order to refresh new data … any ideas???

  6. It works great!!! i’ve been looking for a solution like this for a very long time (apparently wasn’t looking in the right place)

    I want to change the script so that instead of having to manually type the sheet names into the array, it reads them from a range in the sheet, but I can’t make it work.

    I keep getting a “Subscript out of range” error when it tries to copy the sheets to the new file.

    I first tried this code:
    arrSheets = Array(Range(“rngRangeWithSheetNames”).Value)

    Then I tried to generate the string to feed the Array function:
    Dim strArray As String
    strArray = “”

    For i = 1 To Range(“rngRangeWithSheetNames”).Count
    If i Range(“rngRangeWithSheetNames”).Count Then
    strArray = strArray & Chr(34) & Range(“rngRangeWithSheetNames”).Cells(i).Value & Chr(34) & “, “
    Else
    strArray = strArray & Chr(34) & Range(“rngRangeWithSheetNames”).Cells(i).Value & Chr(34)
    End If
    Next

    arrSheets = Array(strArray)

    Any ideas?

  7. @nef, you could use this technique, based on a newsgroup post by Dave Peterson:

       Dim c As Range
       Dim j As Long
             Dim arrSheets()     ‘This is an array definition
        j = 0
        For Each c In Range(“rngRangeWithSheetNames”)
            j = j + 1
            ReDim Preserve arrSheets(1 To j)
            arrSheets(j) = c.Value
        Next c
    
  8. Works great! Thanks a lot!

    Anyway, I still don’t understand the benefit to declaring a variant instead of an array as you did here.

  9. It’s a good resource!
    But I wonder, how many sheets can I consolidate?
    I did the test and only it’s consolidating five sheets.

    Any comments?

    Regards.

  10. @silos
    I’m currently using this to consolidate over 40 sheets without problems, but i do remember reading about someone who did reach a limit (I also think this guy wanted to consolidate something like 500 sheets).

  11. Is there an Excel 2007 version? When I try converting to .xlsm, everything works fine EXCEPT for random number fields will no longer display as a sum of the values in the pivot table (shows all zeros), it will only Count them becuase it’s like Excel no longer realizes that ther’re numbers, even though they are numbers in the source data. This doesn’t occur for all columns with numbers, only a select random few.

  12. To fix the PivotTable Refresh error in XL2003 and XL2007, please change the DriverId from 1046 to 790 in the three instances of the following code line (and in the connection properties if applicable):

    “DriverId=1046;” & _

  13. Kirill and Héctor … you are a darling — just made my most complicated challange a matter of few seconds… thanks again

  14. Hi All,
    Thanks for the great Macro and I need some help.
    I use XL 2007 and when I run this macro it can only pick-up XL 2003 files.
    Is there a way i cam make the Macro also select XL 2007 files.
    I have huge data and 2003 does not have enough row to support.

    Many Thanks.
    Ravi.

  15. Hi Ravi,
    Assuming that you are running the macro from an *.xlsm file, try changing the line:
    strFileTemp = strPath & “DBtemp” & Format(Now, “yyyymmddhhmmss”) & “.xls”
    to
    strFileTemp = strPath & “DBtemp” & Format(Now, “yyyymmddhhmmss”) & “.xlsx”

  16. Very helpful! Thanks
    For arrSheets = Array(“Sheet1?, “Sheet2?)
    Is there any way that it can include the sheets automatically?
    I’m doing a weekly report which means there will be a new worksheet added every week.
    Do I need to enter then name manually?

  17. @Anna
    Check Debras answer (comment #7).

    That piece of code allows you to define a range with all of the sheet names, that way you would only need to add the new sheet name to the list.

  18. Hi Evan, unless you provide a bit more detail about what you’re doing, it’s going to be difficult to help you 🙂 Also, try downloading the new version of the sample file uploaded yesterday.

  19. If the data is in a named range on each sheet (same name on each), it is possible to create the consolidated pivot table by adding the name after the $ in the code. The refresh then unfortunately fails. I think it is due to the named ranges, because the original code runs and refreshes successfully on entire worksheets. Any ideas?

  20. Hi Andrew, worksheet-level named ranges work with SQL queries no problem. Most probably, you are either using a calculated range [e.g.: Alberta!Data=$A$1:INDEX($H:$H,MATCH(“*”,$A:$A,-1))] or a wildcard sheet reference [e.g.: Data=!$A$1:$H$50]. As far as I know, neither of them would be recognized by a SQL query.

  21. No, it’s a normal range name. The initial query works fine (accessing the copied workbook), but the refresh doesn’t. The code in this thread works by changing the connection string back to the current (open) workbook. The problem seems to be with accessing this. I did a further test by opening the workbook as readonly, and in this case the refresh works. There seem to be some situations where the query fails on a currently open workbook.

  22. Hi Andrew, I have tested the code with worksheet-level named ranges in XL2003 and 2010 and have been unable to reproduce the behaviour described by you. Is there possibly any additional circumstance you haven’t mentioned?

  23. I have created a version of your sample workbook, with your two worksheets and two of my client’s. I have created worksheet-level names (‘Data’ in each case) on all 4 worksheets. Using yours, the pivottable can be created and refreshed. Using the other two, it can be created but not refreshed. I can send it to you if you can let me have an email address to use. I am using Excel 2007.

  24. Hi Andrew, it looks like the numeric names of the sheets cause the issue. Try changing worksheet names from “09010910? to “S09010910? and “09130924? to “S09130924? and it will work like charm.

  25. Thanks, this has been a great help!
    I’m continually getting this error when I try to create a new table with the updated version of the code:

    Run-time error ‘1004’
    [Microsoft][ODBC Excel Driver] The number of columns in the two selected tables or queries of a union query do not match.

    I’m not a programmer, but I’m pretty sure I understand enough about this code to know there should be more than two selected tables in my code (about 20 tabs). I’m following the instructions in the file exactly. When I debug, it sends me to this line:
    Set PT = .CreatePivotTable(TableDestination:=ActiveSheet.Range(“A16?))

    Any help would be greatly appreciated!

  26. I double-checked each tab for hidden or extra columns, and they all go to column O. Any other possibilities?

  27. For those of you who want the code to detect the sheets automatically,

    just replace:

    ‘ Sheets to consolidate
    ‘*****************************************************************************
    arrSheets = Array(“310_BWATTS_P Pastujova”, “310_BWATTS_Maria Sanchez”)
    ‘*****************************************************************************

    with the following code:

    ‘ Sheets to consolidate
    ‘*****************************************************************************
    Dim ws As Worksheet
    ReDim arrSheets(0)
    For Each ws In ThisWorkbook.Worksheets
      If ws.Name <> ActiveSheet.Name Then
        arrSheets(UBound(arrSheets)) = ws.Name
        ReDim Preserve arrSheets(UBound(arrSheets) + 1)
      End If
    Next ws
    ReDim Preserve arrSheets(UBound(arrSheets) – 1)
    ‘*****************************************************************************
    
  28. Hmmm…None of the columns were blank, but some of the cells in the 2nd row (subheadings) were blank. I tried deleting that row on each tab, and still no luck. There was one column with no values; I entered zero values for each cell and still got the same error. Then I entered zeroes for all of the blank cells, and I’m still getting the same error. The only thing left I can think of is comments on the cells, but that doesn’t seem like it should affect anything.

  29. You can’t use subheadings (and what’s even worse – merged cells) in tables for this solution to work. There must be no other data on the sheet outside the table. Some headings might look identical while containing extra spaces or alike.

  30. If I may jump in. This superb macro has greatly enhanced my reporting … all my data tables are now structured to use this macro. In my initial setup I encounted similiar issues. I recommend using cntl+end key, which will confirm your last column is indeed the last column of headings. If cntl+end exceeds last heading column, “clear/deleate” that column, save excel, and cntl+end again to confirm last columns now match. Give it a try. Scotty

  31. Hooray! The CTRL+END tip did the trick! Thank you so much everyone for your help! Now this is the best tool we have for planning time across many people and many months.

  32. hi there,
    many thanks for you for this support.
    can i have a suggestion of how to create a sheet or formula that can delete the repeated names or at least change the cell colurs when that name has repeated in the second colomn??
    more description:
    one colomn contains entrance names and the second contains the exit. i need to know who still in there!!!

  33. Hi,

    Got it working yesterday with 2 sheets of data. Have now added several more (don’t know whether that is related or not) but the code now hangs on this line:

    Set PT = .CreatePivotTable(TableDestination:=ActiveSheet.Range(“A16?))

    I have also tried it with the new code to detect sheets automatically (as posted on 10th October).

    I know next to nothing about VBA. Any ideas?

    Looks like

  34. Excelent article and samples

    What happens if the multiple sheets are each in their own workbook ? it seems not to work : (

  35. Hi guys,
    I’m really hoping you can help me. I have for sometime now been trying to automatically refresh a pivot table in Excel 2010. I have tried numerous approaches including generating a macro whilst refreshing interactively – which incidentally works no problem – and re-running it, but the pivot table then fails refresh… I have also tried:

    Example 1:
    Sheets(“xxx”).Select
    ActiveSheet.Unprotect
    ActiveSheet.BackgroundQuery = False
    Range(“C13?).Select
    ActiveSheet.PivotTables(“AllScens”).PivotCache.Refresh

    Example 2:
    Sheets(“xxx”).Activate
    Sheets(“xxx”).PivotTables(“AllScens”).RefreshTable

    Example 3:

    Sheets(“xxx”).Activate
    ActiveSheet.PivotTables(“AllScens”).PivotCache.RefreshTable

    Example 4:

    ActiveWorkbook.RefreshAll

    Example 5:

    ActiveWorkbook.PivotCaches(1).Refresh

    and count less others!!

    And it’s driving me slightly bonkers! In fact it’s a simple set-up where values are simply inserted in a pivot table from another worksheet in the same workbook so there’s no complication in terms of connectivity… My feeling now, although I am pretty much a novice, is that there is something in my Excel setup itself which is overriding these commands. Something to do with allowing background updates perhaps…? Whatever I have searched and tried for days now, and I am starting to sweat as this project is drawing to a close and I don’t know how to explain to my client that something which essentially should be a given is not possible… Anyway I figure you’re all experts, and in particular Kirill’s insight into pivot tables and their peculiarities has caught my attention and just maybe you’ll be able to help me. I will be very grateful for any feedback, many thanks and have a great weekend,

    Louise

  36. Hi Louise,
    If there is only one cache in the workbook, then
    ActiveWorkbook.PivotCaches(1).Refresh
    should do the job.
    ActiveSheet.PivotTables(“AllScens”).PivotCache.Refresh
    is a valid alternative too. What error message do you get upon the execution of the code, or otherwise what makes you think the refresh operation fails?

  37. Hi Kirill,

    Nice to hear from you. The error message I was getting was:

    Run-time error 1004
    Reference is not valid

    I have however in the meantime decided to go back to basics and built the simplest example I could imagine – and low and behold it works using:
    ActiveWorkbook.PivotCaches(1).Refresh

    I am currently now slowly but surely introducing all the other functionality I had in my original workbook, and I will let you know how I get on…
    By the way, no doubt a silly question, but how do I know how many caches are being used?

    Wish me luck and thanks ever so much for your time,

    Louise

  38. Thanks for this code! I used it to consolidate weekly informmation for the last year. Now that a new week has passed, how do I add to my Pivot Table? Is there a way to refresh the list of sheets? I used the following code to define the list:

    [code]
    ‘ Sheets to consolidate
    ‘*****************************************************************************
    Dim ws As Worksheet
    ReDim arrSheets(0)
    For Each ws In ThisWorkbook.Worksheets
    If ws.Name = “SQL” Then GoTo 1
    If ws.Name ActiveSheet.Name Then
    arrSheets(UBound(arrSheets)) = ws.Name
    ReDim Preserve arrSheets(UBound(arrSheets) + 1)
    End If
    1: Next ws
    ReDim Preserve arrSheets(UBound(arrSheets) – 1)
    ‘*****************************************************************************
    [/code]

  39. Hi guys,

    Strange as it may seem I rebuilt my workbook and so far so good – refresh within VBA works! I am of course relieved, but a little perturbed as I still don’t know what went wrong the first time round… Having done a lot of surfing it seems others have had a similar experience so one small comfort is that I’m not alone! 😉

    Thanks Debra for the feedback iro pivot cache count – much appreciated.

    One last point – I want to create a pivot table, but want the order the data is displaed in to remain the same as that in the original data i.e. I have phases in a project arising over a number of years, for example:

    Year Phase {Scenario 1} {Scenario 2} — {Scenario n}

    2010 Verfahren
    2011 Verfahren
    etc.
    2020 Bau
    2021 Bau
    etc
    2030 Betrieb
    2031 Betrieb
    etc
    2040 Verschluss
    2041 Verschluss
    etc
    2050 Monitoring
    2051 Monitoring
    etc

    and I do not want these phases in either alphabetical ascending or descending order in my pivot table i.e. I do not want for example Verfahren 2nd from the end when I filter on Phase. Any suggestions?

    ( Yes, the projects in German so it’s all very interesting! 😉

    And lastly if any of you guys happen to have an example of VBA code which dynamically creates/refreshes such a pivot table and potentially an associated pivot chart ( number of rows and/or columns may vary as may the Scenario names i.e. column header names ) I would be very interested! ( Know I’m asking a lot, but you never know your luck 😉

    Many thanks

    Louise

  40. Hi, i am from panama and don’t speack a lot of english,
    my question is.
    When i open the pivote with the change, only appear the pivote but not the style i change.

  41. Hello,

    I have downloaded the file but am getting “run-time error 9? when I click the Create Empty Table button.

    What am I doing wrong?

    Claire

  42. Kirril…this code is fantastic. I’ve amended it to use different sheets as relational tables, and then build a pivot. Good thing about your code is renaming the ‘database’ (i.e. the workbook) doesn’t cause any problems, because the connection string is updated on file close.

    Only problem is that if you save the file somewhere else or with another name, and you forget to click the ReestablishConnection button, your pivot will be pointing at the ‘old database’. So my minor suggested improvement would be to dump this:
    Private Sub Workbook_Open()
    ReestablishConnection
    End Sub

    …in favour of this:

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim file_name As Variant
    Application.EnableEvents = False

    Dim FName As String
    If Not SaveAsUI Then Exit Sub
    Application.EnableEvents = False
    FName = Application.GetSaveAsFilename
    ThisWorkbook.SaveAs Filename:=FName
    Application.EnableEvents = True
    Cancel = True

    Call ReestablishConnection
    End Sub

    I guess you could also add a check before the Call ReestablishConnection line to see if the connection has changed, such as
    IF Worksheets.PivotTables(PIVOTNAME).PivotCache.Connection = strCon then exit sub
    …but then, by the time you go and declare the variables you need to perform this check, you may as well go ahead and update the connection regardless of whether it ‘needs’ updating.

    Your thoughts?

  43. @Kirill: Worth noting that your code doesn’t set out what version of pivottable should be created, and so excel creates a version 0 PT as far as I can tell. Consequently, pivottable styles don’t seem to ‘stick’. If I apply an excel 2007 pivottable style to your pivots, then save the file, then I get the expected warning message “A Pivottable style is applied to a PivotTable in this workbook. PivotTable style formatting cannot be displayed in earlier versions of Excel.” But when I close and reopen the file in excel 2007 then I don’t see any PivotTable style formatting.

    However, amending the line Set PT = .CreatePivotTable(TableDestination:=ActiveSheet.Range(“A16) to Set PT = .CreatePivotTable(TableDestination:=ActiveSheet.Range(“A16”), DefaultVersion:=xlPivotTableVersion10) will tell excel to create a PT compatible with excel 2002/2003 and also allow PT Style Formats to be saved with the file so that they will show in excel 2007.

    I found some handy info on PT versions at http://blogs.msdn.com/b/excel/archive/2008/02/05/common-questions-around-excel-2007-olap-pivottables.aspx

  44. Whoops, missed something from my comment above.

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Dim FName As String
    If Not SaveAsUI Then Exit Sub
    Application.EnableEvents = False
    FName = Application.GetSaveAsFilename
    If FName “False” Then ThisWorkbook.SaveAs Filename:=FName
    Application.EnableEvents = True
    Cancel = True

    Call Update_Pivot_Connection

    End Sub

  45. Damn…parser ate some of my code. Here it is again:

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim FName As String
    If Not SaveAsUI Then Exit Sub
    Application.EnableEvents = False
    FName = Application.GetSaveAsFilename
    If FName <> “False” Then ThisWorkbook.SaveAs Filename:=FName
    Application.EnableEvents = True
    Cancel = True
    Call Update_Pivot_Connection
    End Sub
    
  46. Damn, it did it again, even though I used code tags.
    There is supposed to be a ‘does not equal’ operator between the text ‘If Fname’ and ‘”False”‘

  47. Hi guys,

    Me again!

    I am experiencing a particularly annoying problem in relation to adding charts to my workbook via VBA. The problem is as follows:

    I have the code:

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlLine
    ActiveChart.SetSourceData Source:=PRange

    where PRange is defined as the range of my Pivottable.

    This code worked well for a while, but on changing my operating system suddenly stared to fail with the message:

    Run-time error: 1004
    Applicatio-defined or object-defined error.

    And once this has occured I can no longer even create a chart manually/interactively.

    If however before running my macro I create any old chart manually/interactively thereafter my macro runs fine and creates the chart generated by the code above. I can also then run it as many times as I wish without this problem arising.

    I can only assume therefore this problem is an initialisation/access problem.

    Has anybody got any idea of how I might solve this?

    Thanks very much,

    Louise

  48. I am trying to use include this in the code as suggested above to automatically read all sheets. There seems to be and error in the line of the If statement. Please help. I am trying to use this on 67 sheets. Thanks!!

    Sheets to consolidate
    ‘*****************************************************************************
    Dim ws As Worksheet
    ReDim arrSheets(0)
    For Each ws In ThisWorkbook.Worksheets
    If ws.Name ActiveSheet.Name Then
    arrSheets(UBound(arrSheets)) = ws.Name
    ReDim Preserve arrSheets(UBound(arrSheets) + 1)
    End If
    Next ws
    ReDim Preserve arrSheets(UBound(arrSheets) – 1)
    ‘************************************************************

  49. I’m having a problem with code that works fine in Excel 2003 & my versin of Excel 2007 – however, another site is trying to use the workbook and the CreatePivotTable command is returning a 1004 – Catastrophic Failure. Does anyone know why this is happening? Code below:

    With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
    .Connection = Array( _
    “OLEDB;Provider=MSOLAP;Initial Catalog=[OCWCube];Connect Timeout=0;PageTimeout=0;Data Source=” & ThisWorkbook.Path & “” & strDSNName & “.cub;CreateCube=CREATE CUBE [OCWCube](“, _
    “DIMENSION [Nurse ID],LEVEL [All] TYPE ALL,LEVEL [Nurse ID], DIMENSION [Doctor ID],”, _
    “LEVEL [All] TYPE ALL,LEVEL [Doctor ID],DIMENSION [Insurance ID],LEVEL [All] TYPE ALL,LEVEL [Insurance ID], DIMENSION [Charge Code ID],LEVEL [All] TYPE ALL,”, _
    “LEVEL [Charge Code ID], DIMENSION [Diagnosis ID],LEVEL [All] TYPE ALL,LEVEL [Diagnosis ID], DIMENSION [Type Of Surgery],LEVEL [All] TYPE ALL,LEVEL [Type Of Surgery], DIMENSION [Charge Code Group], “, _
    “LEVEL [All] TYPE ALL,LEVEL [Charge Code Group],DIMENSION [O Insurance ID],LEVEL [All] TYPE ALL,LEVEL [O Insurance ID], DIMENSION [PC Modifier],LEVEL [All] TYPE ALL,LEVEL [PC Modifier], “, _
    “DIMENSION [Other Code],LEVEL [All] TYPE ALL,LEVEL [Other Code], DIMENSION [Facility ID],LEVEL [All] TYPE ALL,LEVEL [Facility ID], “, _
    “DIMENSION [Service Date] TYPE TIME,LEVEL [All] TYPE ALL,LEVEL [Year] TYPE YEAR,LEVEL [Quarter] TYPE QUARTER,LEVEL [Month] TYPE MONTH, DIMENSION [POS], “, _
    “LEVEL [All] TYPE ALL,LEVEL [POS], DIMENSION [TOS],LEVEL [All] TYPE ALL,LEVEL [TOS], DIMENSION [Acct Date] TYPE TIME, LEVEL [All] TYPE ALL,LEVEL [Year] TYPE YEAR,”, _
    “LEVEL [Quarter] TYPE QUARTER,LEVEL [Month] TYPE MONTH, DIMENSION [Practice ID], LEVEL [All] TYPE ALL,LEVEL [Practice ID], DIMENSION [AC Modifier],LEVEL [All] TYPE ALL,”, _
    “LEVEL [AC Modifier], DIMENSION [Surgeon ID],LEVEL [All] TYPE ALL,LEVEL [Surgeon ID], MEASURE [Total Nurse Time] FUNCTION SUM, DIMENSION [Trans ID],”, _
    “LEVEL [All] TYPE ALL,LEVEL [Trans ID], DIMENSION [Patient],LEVEL [All] TYPE ALL,LEVEL [Patient],DIMENSION [Patient Sex],LEVEL [All] TYPE ALL,LEVEL [Patient Sex], “, _
    “DIMENSION [Fin Class ID],LEVEL [All] TYPE ALL,LEVEL [Fin Class ID], DIMENSION [Patient Age],LEVEL [All] TYPE ALL,LEVEL [Patient Age], “, _
    “DIMENSION [Zip Code],LEVEL [All] TYPE ALL,LEVEL [Zip Code], DIMENSION [Bill Type],LEVEL [All] TYPE ALL,LEVEL [Bill Type], MEASURE [Total Doctor Time] FUNCTION SUM,”, _
    “DIMENSION [O Insurance Group],LEVEL [All] TYPE ALL,LEVEL [O Insurance Group], DIMENSION [O FinClass Id],LEVEL [All] TYPE ALL, “, _
    “LEVEL [O FinClass Id], DIMENSION [Visit Reason],LEVEL [ALL] TYPE ALL,LEVEL [Visit Reason], DIMENSION [Visit User Defined],LEVEL [ALL] TYPE ALL,LEVEL [Visit User Defined], “, _
    “DIMENSION [Anes User Defined],LEVEL [ALL] TYPE ALL,LEVEL [Anes User Defined], “, _
    “DIMENSION [ASA Code],LEVEL [All] TYPE ALL,LEVEL [ASA Code], MEASURE [Charges] FUNCTION SUM, MEASURE [Payments] FUNCTION SUM, MEASURE [Adjustments] FUNCTION SUM, “, _
    “MEASURE [Balance] FUNCTION SUM); InsertInto=INSERT INTO OCWCube([Charges],[Payments],[Adjustments],[Balance],[Nurse ID],[Doctor ID],[Insurance ID],”, _
    “[Charge Code ID],[Diagnosis ID],[Type Of Surgery],[Charge Code Group],[O Insurance ID],[PC Modifier],[Other Code],[Facility ID],[Service Date],[POS],[TOS],[Acct Date],[Practice ID],[AC Modifier],”, _
    “[Surgeon ID],[Total Nurse Time],[Trans ID],[Patient],[Patient Sex],[Fin Class ID],[Patient Age],[Zip Code],[Bill Type],[Total Doctor Time],[O Insurance Group],”, _
    “[O FinClass Id], [Visit Reason], [Visit User Defined], [Anes User Defined], [ASA Code]) OPTIONS ATTEMPT_ANALYSIS SELECT QA.Charges, QA.Payments, QA.Adjustments, QA.Balance, QA.NurseId, QA.DoctorId, QA.InsuranceId, QA.ChargeCodeId, QA.DiagnosisId, “, _
    “QA.TypeOfSurgery, QA.ChargeCodeGroup, QA.OInsuranceId, QA.PCModifier, QA.OtherCode, QA.FacilityId, QA.ServDate, QA.PlaceOfService, QA.TypeOfService, QA.AcctDate, QA.PracticeId, QA.ACModifier, QA.SurgeonId, “, _
    “QA.TotalNurseTime, QA.TranId, QA.PatientName, QA.PatientSex, QA.FinClassId, QA.PatientAge, QA.PatientZIP, QA.BillType, QA.TotalDoctorTime, QA.OInsuranceGroup, QA.OFinClassId, QA.VisitReason, QA.VisitUserDefined1, QA.AnesUserDefined1, QA.ASACode “, _
    “FROM BAMANALYSIS QA ” & strWhere & “; Source_DSN=””DSN=” & strDSNName & “;APP=Microsoftr Query;DATABASE=” + strDSNName + “;Network=DBMSSOCN””;UseExistingFile=false”)

    .CommandType = xlCmdCube
    .CommandText = Array(“OCWCube”)
    .MaintainConnection = True
    .CreatePivotTable TableDestination:=Range(“B1?), TableName:=”ptNHA”
    End With

  50. I started getting an error this week when adding my weekly table and recreating the pivot table. The error I get is:

    Run-time error ‘1004’:
    [Microsoft][ODBC Exel Driver] Query is too complex.

    I don’t understand why I get this error. Each sheet is about the same size. I am using the following code to pick up all sheets (except my SQL tab that i use to dynamically create an SQL query I run to pull the data):

    [code]
    Dim ws As Worksheet
    ReDim arrSheets(0)
    For Each ws In ThisWorkbook.Worksheets
    If ws.Name = “SQL” Then GoTo 1
    If ws.Name ActiveSheet.Name Then
    arrSheets(UBound(arrSheets)) = ws.Name
    ReDim Preserve arrSheets(UBound(arrSheets) + 1)
    End If
    1: Next ws
    ReDim Preserve arrSheets(UBound(arrSheets) – 1)[/code]

    Any ideas? Did I somehow find the max allowable sheets (I don’t think so, others have reported more than 53 sheets).

  51. As a followup, I verified I am not able to have more than 50 sheets. If I remove any sheet, the Pivot Table will be created correctly, but the 51st sheet results in the “Query is too complex” error.

  52. I found a workaround (not pretty), which brought up related questions.

    The issue I had was with 50+ Union All statements…SQL doesn’t allow that many. I was able to do them in sections, then Union them all together at the end. I had to build it to support more or less than 50, and now it appears it will work with as many tabs as Excel can hold.

    The issue I would still like to solve is related to run time…the generation of a temp file that has 50+ sheets takes minutes, thus causing this Macro to run long. I tried doing away with temp file, but I couldn’t hold a connection to the current file…and I don’t know enough to understand why.

    Here is the semi-final code I am using, which works(slowly).

    Sub CreateConnection()
    Dim PT As PivotTable
    Dim PC As PivotCache
    Dim strFile As String
    Dim strFileTemp As String
    Dim strPath As String
    Dim arrSheets As Variant
    Dim strSQL As String
    Dim strSQLtemp As String
    Dim strCon As String
    Dim i As Long
    Dim objRS As Object

    ‘ Sheets to consolidate
    ‘*****************************************************************************
    ‘arrSheets = Array(“Ontario”, “Alberta”)

    Dim ws As Worksheet
    ReDim arrSheets(0)
    For Each ws In ThisWorkbook.Worksheets
    If ws.Name = “SQL” Then GoTo 1
    If ws.Name ActiveSheet.Name Then
    arrSheets(UBound(arrSheets)) = ws.Name
    ReDim Preserve arrSheets(UBound(arrSheets) + 1)
    End If
    1: Next ws
    ReDim Preserve arrSheets(UBound(arrSheets) – 1)

    ‘ Sheets to consolidate
    ‘*****************************************************************************

    If Val(Application.Version) > 11 Then
    DeleteConnections_12
    CheckFileFormat_12
    Else
    strFileExt = “.xls”
    lngFileFormat = xlNormal
    End If

    Application.ScreenUpdating = False
    With ThisWorkbook
    strPath = .Path
    strFile = .FullName
    strFileTemp = strPath & “DBtemp” & Format(Now, “yyyymmddhhmmss”) & strFileExt
    ActiveSheet.Cells.Clear
    .Worksheets(arrSheets).Copy “‘This takes a LONG time
    End With

    With ActiveWorkbook
    .SaveAs strFileTemp, lngFileFormat “‘This takes a LONG time
    .Close
    End With
    ‘strSQL = “”
    For i = LBound(arrSheets) To Application.WorksheetFunction.Min(i + 45, UBound(arrSheets))
    If arrSheets(i) ActiveSheet.Name Then
    If strSQL = “” Then
    strSQL = “(SELECT * FROM [” & arrSheets(i) & “$]”
    Else
    strSQL = strSQL & ” UNION ALL SELECT * FROM [” & arrSheets(i) & “$]”
    End If
    End If
    DoEvents
    Next i
    strSQL = strSQL & “) a”
    strSQL = “Select * from ” & strSQL
    “”‘Next section accounts for sheets > 45″”‘
    Do While i <= UBound(arrSheets)
    strSQLtemp = “”
    If i <= UBound(arrSheets) Then
    For i = i To Application.WorksheetFunction.Min(i + 45, UBound(arrSheets))
    If arrSheets(i) ActiveSheet.Name Then
    If strSQLtemp = “” Then
    strSQLtemp = “(SELECT * FROM [” & arrSheets(i) & “$]”
    Else
    strSQLtemp = strSQLtemp & ” UNION ALL SELECT * FROM [” & arrSheets(i) & “$]”
    End If
    End If
    DoEvents
    Next i
    strSQLtemp = strSQLtemp & “) b”
    strSQL = strSQL & ” UNION ALL ” & “Select * from ” & strSQLtemp
    End If
    Loop

    “”End of sheets > 45″”‘
    strCon = _
    “ODBC;” & _
    “DSN=Excel Files;” & _
    “DBQ=” & strFileTemp & “;” & _
    “DefaultDir=” & strPath & “;” & _
    “DriverId=790;” & _
    “MaxBufferSize=2048;” & _
    “PageTimeout=5?
    MsgBox ThisWorkbook.PivotCaches.Count
    Set PC = ThisWorkbook.PivotCaches.Add(SourceType:=xlExternal)

    With PC
    .Connection = strCon
    .CommandType = xlCmdSql
    .CommandText = strSQL
    Set PT = .CreatePivotTable(TableDestination:=ActiveSheet.Range(“A16?))
    PT.Name = “TestPivot”
    End With

    With PT.PivotCache
    .Connection = Replace(strCon, strFileTemp, strFile)
    End With

    ‘Clean up
    Kill strFileTemp
    Set PT = Nothing
    Set PC = Nothing
    End Sub

  53. Joshua,
    this is great and helped me out a ton. I’m combining sheets from different files but this workaround worked really well for me.
    I pull in sheets from 100+ files in less than ten seconds. I don’t copy the sheets, I just read them. So maybe that’s why it’s so much faster.

  54. How do you “just read them”? I am a bit of a hack when it comes to VBE…I can reverse engineer and solve, but I don’t have all the knowledge I need….I would prefer to just read them, copying them then deleting the copy is a comprise I make to get the job done (and it takes about 5 minutes more!)

  55. This works great. I have 6 worksheets with 20,000 lines of data each. But….

    I just realized that the pivot doesnt pick up values that are not numbers. For example in one column I have a combination of 4-digit numbers (e.g., 4005) and words (e.g., NOLA).

    Does anyone know how to fix the VBA code to include the text items found in my column???

    I am using MS Excel 2003 on Windows XP operting system at work.

    ThankS!!

  56. Marie, I had similiar issue. Make sure data columns are formated as value i.e. format cells as “number”. Make sure “label” columns are formated as text i.e. “general”. If you refresh your data set, you may have to ensure columns are reformated each time before you refresh pivots. Scotty

  57. KL thank you so much. This has helped me a ton. I do have a question. I have set up my array for 5 sheets of data. Everything works great if all 5 sheets are populated. If one or more of the sheets is left blank I get a divide by 0 error (#DIV/0!). Is there a way I can avoid this error by changing the code to ignore blank sheets in the array?

  58. Thanks so much. I am having problems with add in another pivot column for Net Count. It can’t sum up the pivot table values properly. Only one column data is used to sum. (actually there are 5 column which need to sum). Anyone can help me out?

  59. I’m now looking at updating my macro based on all the great posts here. I’m running in to a compile error in the code to detect sheet names automatically. Has anyone else run in to this?
    Expected: Then or GoTo
    for this line: If ws.Name ActiveSheet.Name Then

    and
    Expected: list separator or )
    for this line: ReDim Preserve arrSheets(UBound(arrSheets) – 1)

    lines are from this:
    Dim ws As Worksheet
    ReDim arrSheets(0)
    For Each ws In ThisWorkbook.Worksheets
    If ws.Name ActiveSheet.Name Then
    arrSheets(UBound(arrSheets)) = ws.Name
    ReDim Preserve arrSheets(UBound(arrSheets) + 1)
    End If
    Next ws
    ReDim Preserve arrSheets(UBound(arrSheets) – 1)

  60. Also, in Jeff’s code to update the connection, I’m getting a Sub or Function not defined error on:

    Call Update_Pivot_Connection

    I don’t actually know any VB, but I can cut and paste really well! Any ideas on how to fix this one?

  61. @Maria…my bad: Update_Pivot_Connection is the wrong name …I should have put ReestablishConnection

    I’ve updated my code since I first posted here. To try my approach, you need to add these two subs to the workbook:

    Copy this first sub into the ThisWorkbook module:

    Option explicit
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If Not SaveAsUI Then Exit Sub
    Application.EnableEvents = False
    Call save_file
    Application.EnableEvents = True
    Cancel = True
    Call ReestablishConnection
    End Sub

    and copy this into a standard module:

    Sub save_file()
    ‘Working in Excel 2000-2010
    Dim fname As Variant

    Dim FileFormatValue As Long
    Dim FilterIndexValue As Long

    ‘Check the Excel version
    If Val(Application.Version) < 9 Then Exit Sub
    If Val(Application.Version) < 12 Then

    ‘Only choice in the “Save as type” dropdown is Excel files(xls)
    ‘because the Excel version is 2000-2003
    fname = Application.GetSaveAsFilename(InitialFileName:=ActiveWorkbook.Name, _
    filefilter:=”Excel Files (*.xls), *.xls”, _
    Title:=”Save as…”)

    If fname False Then
    ‘We use the 2000-2003 format xlWorkbookNormal here to save as xls
    ActiveWorkbook.SaveAs fname, FileFormat:=-4143, CreateBackup:=False

    End If
    Else
    ‘Need to get excel to pre-set the filterindex setting to match the existing file, rather than have a hard-coded value.
    ‘For some reason if the current extension doesn’t match the default filterindex setting then
    ‘the activeworkbook.name is returned in quotation marks, and whatever the user selects from the filterindex
    ‘dropdown has no effect unless those quotation marks are removed.
    Select Case LCase(Right(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) – InStrRev(ActiveWorkbook.Name, “.”, , 1)))
    Case “xlsx”: FilterIndexValue = 1
    Case “xlsm”: FilterIndexValue = 2
    Case “xls”: FilterIndexValue = 3
    Case “xlsb”: FilterIndexValue = 4

    Case Else: FilterIndexValue = 3 ‘make this the default in the event that we get no match.
    End Select

    ‘Give the user the choice to save in 2000-2003 format or in one of the
    ‘new formats. Use the “Save as type” dropdown to make a choice,Default =
    ‘Excel Macro Enabled Workbook. You can add or remove formats to/from the list

    fname = Application.GetSaveAsFilename(InitialFileName:=ActiveWorkbook.Name, filefilter:= _
    ” Excel Macro Free Workbook (*.xlsx), *.xlsx,” & _
    ” Excel Macro Enabled Workbook (*.xlsm), *.xlsm,” & _
    ” Excel 2000-2003 Workbook (*.xls), *.xls,” & _
    ” Excel Binary Workbook (*.xlsb), *.xlsb”, _
    FilterIndex:=FilterIndexValue, Title:=”Save as…”)

    ‘Find the correct FileFormat that match the choice in the “Save as type” list
    If fname False Then
    Select Case LCase(Right(fname, Len(fname) – InStrRev(fname, “.”, , 1)))
    Case “xls”: FileFormatValue = 56
    Case “xlsx”: FileFormatValue = 51
    Case “xlsm”: FileFormatValue = 52
    Case “xlsb”: FileFormatValue = 50
    Case Else: FileFormatValue = 0
    End Select

    ‘Now we can create/Save the file with the xlFileFormat parameter
    ‘value that match the file extension
    If FileFormatValue = 0 Then
    MsgBox “Sorry, unknown file extension”
    Else
    ‘Save the file in the format you choose in the “Save as type” dropdown
    ActiveWorkbook.SaveAs fname, FileFormat:= _
    FileFormatValue, CreateBackup:=False
    End If
    End If
    End If
    End Sub

    Hopefully that will do the trick. Let me know if you have any problems with this. I can email you a workbook if you like…you can email me at weir dot jeff at gmail dot com

  62. Note you’ll have to replace the curly quotation marks with straight ones (the wordpress parser changed them to the wrong ones). I’ll upload a workbook with this code and paste a link here, after I’ve checked it works.

  63. Hi All, Back Again!This macro has been working wonderfully for me. And management at work love the ability to see every month of data consolidated into one pivot table. However, I received an error and I am not sure how to fix. I just added my ninth tab to my excel file. There will only be 12 (one for each month) and the macro has stopped working. It stops at the following line in the code: .Worksheets(arrSheets).Copy

    It came from the following subset:

    With ThisWorkbook
    strPath = .Path
    strFile = .FullName
    strFileTemp = strPath & “DBtemp” & Format(Now, “yyyymmddhhmmss”) & strFileExt
    ActiveSheet.Cells.Clear
    .Worksheets(arrSheets).Copy
    End With

    I never had the problem before so I’m not sure why it is happening now. I am using MS Excel 2003 on Windows XP operting system at work. Any help would GREATLY appreciated.

    Thanks!!

  64. Ni Marie,

    Could you please confirm what is the error you get when the macro stops and also show the arrSheets setting part you’re using in your code, you know, this line:

    arrSheets = Array(“Ontario”, “Alberta”)

    The problem must be there.

  65. This is fantastic. I am trying to set this up for my wife’s company to use to keep track of her sales team’s commissions. In testing it works great except I am having a problem with one thing. For some reason when the macro creates the pivot table, the date column is not being recognized as date data. When I try to filter the date column it only shows value filters and the date filters is greyed out. Is this a limitation of the macro or am I doing something wrong? In another file, when creating a pivot table with the same data the column is recognized correctly as dates. Any help would be great. Thanks in advance

  66. Hi Matt,

    This is probably your data problem and Pivot Tables limitation. You either have some dates in text format or cells looking like empty, but actually containing zero-length text strings, in the date filed. Can you please check that?

  67. Kirill

    Thanks for the quick reply. I checked the data and the column is formatted as a date column. I also checked and there are no text strings in them. I redownloaded the sample file with the Ontario and Alberta data and even that the dates dont seem to be correctly formatted to allow for the date filter. Any ideas? Need me to send you a file to show you what I mean?
    Thanks again
    Matt

  68. I’ve tried using this to combine data from different sheets, where each sheet has the same two columns on the left but the remainder of columns are different – the file mentioned an union query doesn’t seem to work in this case. Can you sugget an alternative means of combining this data without having to put it all in one enormous table?

    E

  69. @E – if you post a link to a sample workbook outlining what you require, I’ll take a look at it for you.

  70. The issue I’m having is when I hit the create empty table button I get the following error: Run Time Error ‘9’: Subscript out of Range. When I hit the Debug button the following line of code is highlighted:
    “.Worksheets(arrSheets).Copy”

    any ideas?

  71. The names on the sheets are identical to what I inserted into the program. I am not sure what you mean by indices.

  72. @Mark: You can refer to a sheet by name or by index ( e.g.: Worksheets(“Sheet1?) or Worksheets(1)). Please show the part of the code that assigns sheet names to the variable arrSheets()

  73. hi debra et al, I am trying to get your code to work inside another workbook. I am attempting to consolidate two named ranges (both have the same name at sheet level). however, when the code gets to the line Set PT = .CreatePivotTable(TableDestination:=ActiveSheet.Range(“A3”))
    within the createconnection macro. it spits up a run time error 1004 stating that the object ‘ImportData$NewBusiness’ could not be found. Could it be that the macro is looking for this named range within the temporary file created or is it more likely to be because my named ranges, while having the same column headings, are not positioned in the same place on each worksheet. ie on the first of the two worksheets, the named range starts at A1 and on the second sheet, it starts at C1. Neither range is contiguous. any ideas?

  74. @ajm
    I haven’t done the testing, but I seem to recall that calculated named ranges would not be recognised by the JET SQL. So if your named ranges contain a formula like =A1:INDEX(D:D,COUNTA(A;A)), then chances are that this is the problem. If that is the case, try to change the formula by a plain range reference (e.g. $A$1:$D$100) and, if it works, then the workaround for you would be to read the dynamic range’s address into a string variable in VBA and then concatenate it into the SQL string variable.

  75. thanks for the quick respons KL. I did see Andrew’s initial query and your response above, and have my ranges named accordingly; that is, by plain range references. I then thought maybe it was because my sheet names each have a space in them, so I added a second word to each of your sheet names in the sample workbook. that still worked, so it wasn’t the sheet names. I have just had another crack at it. Initially, i made the two ranges in your sample workbook into Named Ranges called “SalesResults”; the code worked perfectly. Now my ranges are non contiguous, so I inserted a column here and there in the data on both sheets and then amended the range references to include only the original data from the tables on each tab. Result: Ontario$NewBusiness could not be found.

    I don’t suppose you would have a work around for this problem?

    ajm

  76. @Kirill

    I ma having the same issue as mark and connot fix. I have my formula listed as arrSheets = Array(“Scrap”,”Zinc”). Please advise on how to fix. Tank you in advance!

  77. I’m a bit of a novice in vba but looks like this would be immensely helful with some reporting I’m doing but can’t seem to get the code to work. I get a “Runtime error 1004, too many fields defined” at this line in the code:
    Set PT = .CreatePivotTable(TableDestination:=ActiveSheet.Range(“A16?))
    when “Creating Empty Table” and can’t seem to figure out the problem. Ultimately I will be consolidating several (~20+) worksheets with thousands of rows each but have reduced the number of worksheets to 2 and rows & columns to what would seem manageable but still get the error. Am running 2003. Any thoughts would be greatly appreciated.

  78. What a Fantastic code. Thanks KL for sharing it.
    Thanks also to the others for commenting it. I tested it and it works great!

    I implemented it at my work.

    A small problem: I try to refresh the testpivot by a macro instead of right click and refresh but I am not successfull:
    ActiveSheet.PivotTables(“TestPivot”).PivotCache.Refresh doesn’t work. Neither ActiveWorkbook.PivotCaches(1).Refresh.
    Even if I record a workable refreshing action, replaying the macro doesn’t work… In some case I get the error teh connection for viewing your linked Microsoft Excel Worksheet was lost..

    Any idea? Thanks in advance for any help.
    Kind regards
    Philippe

  79. I have a new project where I am using this functionality again. I am having a heck of a time with 2 areas:
    #1. If there is a random space/odd character in a cell, columns won’t sum correctly. they sum fine in normal column totals within the sheets but not on the Pivot…any ideas?
    #2. If I refresh the data most of my columns no longer sum and I have to start all over building the PT from scratch…how can I find the issue?
    Thanks
    Joshua

  80. Hi all
    I am really struggling to get this to consistently work. I have a team of 8 people and we all keep daily timesheets to allocate our spend against different projects. Each person has their own pivot but I wanted one pivot to show everyones spend against all projects. I get various errors:
    Run time 1004
    the code Set PT = .CreatePivotTable(TableDestination:=ActiveSheet.Range(“A56”)) highlights
    ODBC Excel Driver Too many field defined
    I am copying the code from above and I am clueless as to solve the problem.
    I have tried copying the data from the other worksheets by: copy tab, copy and paste to new sheet, copy and paste values.
    Help please! It would be much appreciated!!!
    Laura

  81. @Laura
    How many columns do you tables have and how many tables are you trying to consolidate please?
    Regards,
    KL

  82. @Joshua,
    Problem is SQL is guessing field data types and, if strange characters are found, it will assume string type and won’t sum. You may need to add some data integrity checking/correcting to the code. One option is to use a loop with TextToColumns method of the Range object on each of the numeric fields before making a connection.
    Regards,
    KL

  83. @Philippe
    Could you please place a sample file with this problem on a shared server (e.g. Mediafire) and publish the link so I can have a look?
    Thanks,
    KL

  84. Hi Kirill,
    First of all, Thanks so much for the great piece of code.
    I am facing the same Run time 1004 as Laura on line Set PT = .CreatePivotTable(TableDestination:=rng(6, 1))
    Number of columns in each table are 52 and number of files I am trying to combine are 4.
    Issue is that, if I try with upto 2 files, the code works perfect. The moment I try the code with 3 files, I get this error.
    Please help.
    Thanks,
    Sharma

  85. Hi Kirill,
    Yes, the tables have identical structure and the same column headings. I checked the code that you have attached here. I think this code is for multiple sheets in the same workbook while I am trying to create the pivot from multiple files/workbooks.
    The reason why I would like to maintain different workbooks instead of having different worksheets in the same workbook is that the file size that I have is pretty large hence keeping only one workbook with many worksheets slows down the speed.
    The error I get is 1004: [Microsoft][ODBC Excel Driver] Too many fields defined and when I debug, it takes me to the line:
    Set PT = .CreatePivotTable(TableDestination:= rng (6, 1))
    I had downloaded the folder for multiple files pivot on page http://blog.contextures.com/archives/2010/08/30/macro-creates-excel-pivot-table-from-multiple-files/
    Thanks for the prompt response and help. 🙂
    Best,
    Sharma

  86. Kirill,
    Thanks so much for the amazing code. I’m having a peculiar issue with the ADO file (plug-and-play) and was hoping you could help me sort it out.
    I’m using the file to compile 32 sheets into a pivot. Two of the data columns receive the “number stored as text” error repeatedly, even after I have reformatted the cells. I’ve tried refreshing the table as well as saving, closing and reopening the file after reformatting the cells with no luck. The strangest thing about this error is that it only happens when compiling multiple sheets. The table has no problem performing calculations as long as only one sheet is being used as input at a time. To add to the mystery, this is only occurring with two specific columns of data. The rest all work magnificently.
    Any ideas as to what could be happening?
    Thanks so very much.
    Best,
    Bill

    1. Bill
      January 9, 2012 at 10:30 am · Reply:
      hi bill
      I had this issue and worked out that it was because of the alignment on the cells. something to do with if you centre align Excel will take it that its text and not numbers bo matter what ‘number format’ you give it . I aligned everything numerical to the right and solved my problem – hope it does yours.
      Laur

  87. Hello,
    The code is working wonderfully for me so far. One error I am encountering is an Out of Memory message. I am incorporating 3 tabs into 1 pivot table and noticed the 3rd tab is not showing data in the pivot table. Can you help with this?

  88. Hi Kirill,
    I would like to ask where to actually edit these lines:
    ‘ Sheets to consolidate
    ‘*****************************************************************************
    arrSheets = Array(“Ontario”, “Alberta”)
    ‘*****************************************************************************
    Set PT = .CreatePivotTable(TableDestination:=ActiveSheet.Range(“A16”)
    I could not find any macro recorded in this file. However, when I right click the buttons (like the “Create Empty Table”, “Delete Table” etc), I clicked the “Assign Macro” then “edit”. A window popped up and I suppose that is where I should edit? However, when I try to save it, it always tell me this error:
    “some cells or styles in this workbook contain formatting that is not supported by the selected file format. These formats will be converted to closest format available.”
    I clicked “continue” nonetheless. The buttons would not then work. The error messages states that “connection could not be established.” and “Pivot table ‘mypivot’ not found”.
    Can you please advice me as I have an urgent project to submit and it is required to use database from two different worksheets in the same workbook.
    Many thanks! Hope to hear from you soon.
    Andrea.

  89. Hi Andrea,
    I suggest you follow Debra’s advise or otherwise try to follow these steps:
    1) save the file on your hard-drive in a folder other than system folders or the root of the system drive (C:\)
    2) open the file allowing macros
    3) hold Alt-key and press F8-key (Alt+F8) to open the VBA editor window
    4) On the left side in the Project explorer window (Project – VBA Project) find the name of your project: ‘VBAProject(VBA.MULT_CONS_PIVOT_EN.xls)’
    5) click on the ‘+’-sign on its left to expand its objects hierarchy
    6) find the folder ‘Modules’ and expand it too
    7) double-click on the object ‘Module 1’ to open the module window at the right
    8) you should now be able to find the code to be replaced
    P.S. The dialog that pops up when you try to save the file is not an error, but a warning about some functionality loss if opened under XL2003 or earlier.
    Hope this helps,
    Regards,
    KL

  90. Hi all
    Thanks to all your help so far my pivot has been running for 5 months now perfectly: I am using it to take working hours information from seperate timesheets and to consolodate the info.
    However I now have a new member of staff so I have placed a new spreadsheet in the folder (copied from another staff members) I then open my report and clicked pivot refresh. it didn’t find the new sheet. So I clicked on the create pivot and it returns nothing
    what have I done wrong? How can I add more workbooks for the pivot to read
    thanks
    Laura

  91. Hi Laura,
    It sounds like you are talking about another post: http://blog.contextures.com/archives/2010/08/30/macro-creates-excel-pivot-table-from-multiple-files/ (this one deals with Pivot Tables based on multiple sheets inside one workbook). That file had a dialog to open the source files from a directory at the PT creation time. In order to add workbooks to your PT, you will need to write a specific routine using Application.GetOpenFilename method or hardcode the files’ paths on a spreadsheet or inside the macro and pass them as arrFiles variable. In Excel 2007-2010, you can also manually update the SQL string by
    – selecting a cell inside your existing Pivot Table
    – going to the Ribbon’s ‘Data’ tab
    – pressing ‘Properties’ button
    – selecting ‘Definition’ tab of the dialog that would pop out
    – and editing ‘Command text’ box
    Hope this helps.

  92. Hi your macro is excellent, working wonders with it .. thank u .. but i have a small prob .. i consolidate data and give a manual refresh , i have a column “Billed Hrs” in my sheet which shows me values in date format .. Tried with both your Pivot Table samples. Could u Help me on that ???

  93. Hello Everyone,
    I trust you are all well. I need some help. How do I get this to work in Excel 2010?
    Thank you very much.
    Bert

  94. @Bertrand…it does work in Excel 2010 already. What kind of error are you getting?
    Note that in Excel 2010, then you can also use a FREE microsoft add-in called PowerPivot to do this, with no VBA required. In case you haven’t looked in to it yet, PowerPivot for Excel is an add-in to Excel 2010 that provides the foundation to import and combine source data from any location for massive data analysis on the desktop, including relational databases, multidimensional sources, cloud services, data feeds, Excel files, text files, and data from the Web.
    The data that you add to the workbook is stored internally, as an embedded PowerPivot database inside the .xlsx file. From a user perspective, key points are:
    1. It allows data people to quickly create mashups of disparate data source on the fly – including web data, text files, datawarehouse files, and excel tables – and serve it up as a pivottable. (Which you can also do with ADO as per my comments above)
    2. It extends the capability of Excel 2010 so that users can crunch, filter, sort millions of records with very low overhead, as well as incorporate both relational and non-relational data sources easily into analysis, using an interface they are already familiar with.
    3. It can be programmed with SQL or DAX, but users don’t need to write SQL or DAX queries in order to use it (although they can if they want).
    Behind the scenes, Powerpivot creates OLAP cubes, and draws heavily on components of SQL Server to do this, but no istall of SQL Server is required. This addin extends the capability of Excel so much that it might well remove the organisational need for some of the other apps in some cases (such as SAS) and make their licencing costs disappear.

  95. @ Jeff
    Thanks for your response. You are right about the PowerPivot. I have spent many days trying to get around an error message I got after installing it. I have read lots of material about and potential fix and none resolved it. The error is “Load Behavior: Not loaded. A runtime error occurred during the loading of the COM add-in”. May be you or someone can offer some insight.
    Thanks.
    Bertrand.

  96. @Bertrand, I hope this helps to resolve your PowerPivot issue.
    File->Options->Add-Ins
    Manage:Disabled Items
    Delete “PowerPivot for Excel” from de list
    Manage:COM Add-ins
    Enable “PowerPivot for Excel”
    Regards

    1. Thanks Elias. It works now. Could you please help me create the pivot tables in Excel 2010 using multiple worksheets?

  97. @Jeff or Anyone
    Could you please help do this in Excel 2010? I am new to VBA programming.
    Thanks.
    Bertrand

  98. @ Jeff or anyone
    To answer your question about what error I get. I was trying to copy my files into the workbook so that I could run the macro only to find that the workbook (with the Ontario and Toronto tabs) have only about 65K rows (Excel 2007). I know I will have to run the code out of my 2010 workbook but like I said, I am not well versed in VBA programming to make the necessary changes to the code for it to work. So I will be grateful if help can be provided or if someone has already used it in 2010 workbook, they can share their insight.
    Thanks.
    Bert

  99. @Bertrand…just resave the workbook as an “Excel Macro-enables Workbook” i.e. with a .XLSM file extention, and then reopen it, and you will see it now has 1.2 Million Rows. The code itself doesn’t care what version of Excel you have.

  100. @Jeff.
    Thanks. I am currently getting the error ODBC Excel Driver Login Failed: External table is not in expected format. Can anyone help?
    Thanks again.
    Bert

  101. I stepped through the code and it seems to error here (on the 5th line)
    With PC
    .Connection = strCon
    .CommandType = xlCmdSql
    .CommandText = strSQL
    Set PT = .CreatePivotTable(TableDestination:=ActiveSheet.Range(“A1”))
    PT.Name = “TestPivot”
    End With

  102. Hello Everyone,
    The piece of code provided by Kirill to automatically get the sheets has an error. On the first IF-THEN clause and last REDIM statement seems to be where the errors are. I know some people have already asked and didn’t get a response. I will appreciate any help to fix this.
    Thanks.
    Bert
    ‘ Sheets to consolidate
    ‘*****************************************************************************
    Dim ws As Worksheet
    ReDim arrSheets(0)
    For Each ws In ThisWorkbook.Worksheets
    If ws.Name ActiveSheet.Name Then
    arrSheets(UBound(arrSheets)) = ws.Name
    ReDim Preserve arrSheets(UBound(arrSheets) + 1)
    End If
    Next ws
    ReDim Preserve arrSheets(UBound(arrSheets) – 1)
    ‘*****************************************************************************

    1. @Bertrand, which sample file did you download, and where is snippet of code located in that sample file?
      Also, are you using 64-bit Excel 2010 or 32-bit? If 64-bit, there might be a problem with the ODBC drivers.

  103. @ Debra
    Thanks for your response. The sample file I downloaded is the one uploaded for PT0022on 24-Aug-09 . The code (by Kirill) to automatically read in the names of the sheets into the array was posted on this blog. I agree that there’s a problem with the ODBC drivers as I do not see the drivers for xlsx, xlsm, etc listed in the ODBC administrator. I will look into fixing that.
    Thanks.
    Bert

    1. @Bertrand, Thanks for the details on which sample and code you’re using.
      I’ve found the problem, and put the corrected code at the end of the blog post, above. Kirill posted the correct code, but the blog’s formatting messed up a couple of things.

  104. Hello,
    I would like to know if it’s possible to copy a pivot table from one sheet and paste it into in another sheet such that two pivot tables are not linked i.e. if I create a calculated item in pivot table it doesn’t show as an item in another.
    Thanks.
    Bertrand

  105. @ Debra
    Awesome but I will need some help. I am currently using the code you and Kirill provided to consolidate multiple sheets into a pivot table. I created some pivot tables by copying and pasting the empty one created by the code. They all use the same cache now but I would like some of them to have a different cache. I will need your help, if possible.
    Thanks.
    Bertrand

  106. Hi,
    I’ve been using the ADO version of the file and having a fantastic time with it.
    One issue I thought I’d raise is with the “Select Database Sheets” list. At this time, the size of the list is not dynamic and therefore the number of sheets that can be used is limited by the amount of screen real estate you have available.
    Is there any potential fix for this?
    Thanks in advance,
    Bill

    1. Solved this; very simple. Just have to adjust the zoom in the Userform line of the Createpivot code. If interested, see below…
      Sub CreatePivot()
      UpdateCache = False
      UserForm1.Zoom = 80
      UserForm1.Show
      End Sub

  107. Hi,
    I have been trying to use your Macro for multiple pivot sheets in Excel. I had deleted the sample data and inserted my own. In total there were 11 sheets, each with identical column headings, and only the row lengths on each of the sheets were different.
    When I run the Macro, I get the follwoing error: “All tables must have identical column names!”.
    Can you please help. I am new to all of this VBA script.
    I am happy to send across the file for you to have a look at.
    I am using the new ado file which was received from the following link: http://www.contextures.com/ExcelTemplates/VBA_PT_NormalMultipleSheets%20EN%2007.zip

  108. Is it possible to specify the area where the data is located?
    I want the data-area to be specified from coloumn 7 til 20, from row 30 til say 200
    Position is identical across the worksheet.
    Thanks for your help!
    Regards

  109. Thanks for your quick respons! No, it doesnt care. But if there is any other information in the sheet not belonging to the table – it all goes wrong as the code selects everything in the worksheet 🙂
    I was wondering if it was possible to limit this to a certain area. From SELECT * to something more precise. I tried to google other ways, without any luck. To bad as this code is perfect in every way except this!

  110. Yes, that’s certainly possible. What are the names of the columns you want to pull through? e.g. Age, Country, State etc. Also, what version of Excel do you have? IF 2007 or later, is the data in each sheet in Excel tables?

    1. I’m using excel 2010. There’s quite a few columns – but age, country, state is a example i can modify 🙂
      The data is not in a excel-table. But that could be fixed without major trouble. I think…

  111. Hi Christian. I’m thinking it would be better to ask this in a help forum, and also set out a bit more on what you are trying to achieve, because this code might be overkill for your needs. Also, I suggest you look at http://chandoo.org/forums/topic/creating-a-priority-table-from-multiple-tables where I helped someone pull together data from seperate tables on different sheets into one. This could then be used to drive a pivot table.
    Failing that, someone over at http://chandoo.org/forums (perhaps me) will be only too glad to take a look. Just be sure not to cross-post, and also upload a sample workbook with non-confidential data and post a link to it on the forum.

  112. I used the “plug and play” download and was able to create my pivot table easily. One issue that I’m having is that I want to total dollars by customer and all i’m getting is “$-” in the table. When I change this to “Count” I get a number, but when I ask it to “Sum” it will not. Is there a fix?

  113. Dear Debra,
    I have 3 points. The 1st is a ‘thank you’ from me to you’ & the other 2 are questions. Having never used VBA before joining my current company I’ve been on a vertical (overhanging?) learning curve for the past 18 months. The reality is that without VBA I would not be able to do my job (I create a full set of Management Reports monthly in Excel – but that’s another story). Your website is one of my ‘reference points’ and I have learned a lot for which I am exceptionally grateful. I’m therefore constantly ‘pushing the boundaries’ of what I know so there are large gaps in my knowledge as my learning has not been structured other than by necessity. I think I will always use VBA but now need to learn a little about SQL.
    I’ve reviewed the code in this spreadsheet & understand most of it but not the elements dealing with the data connections/SQL, etc. My 1st question. I’ve found lots of generic SQL courses on the web & have bought a currently indecipherable ‘dummies’ SQL book but are there any resources (web/books/course) that specifically handle this sort of data management (i.e. managing excel data rather than ‘pure’ Db stuff like Access)?
    My 2nd question, a previous ‘blogger’ mentioned using named ranges rather than worksheets. I would like to do this as I have data sheets with additional information which I don’t want to consolidate (FX/reconciliations, etc). So, is it possible to consolidate only the data area of an Excel table and if so what’s the syntax (I’ve tried & failed).
    Regards

  114. Dear Kirill Lapin, Hector and Deborah!
    Thank you very much for sharing your experience.
    You guys save my life 🙂
    all of you are Awesome!
    Thanks again and keep doing what you are doing so gggreat!
    GN

  115. Hi All,
    Spent some time to get this work, brilliant option to create a pivot with multiple worksheets.
    I just encountered a problem with next step, to create Slicers for the pivot.
    Unfortunately Slicer option is “Greyed” for pivots created using connection option but if I create a pivot directly from a data worksheet, it’s available. The file is saved as a .xlsm in Excel 2010.
    Any suggestions please.

  116. Hi Perera
    I have the same problem. I’m thinking it has something to do with the type of pivot table it creates (an older version) but I have been unable to upgrade to the excel 2010 version of pivot tables.
    Any help would indeed be welcome

    1. I got it to work by adding this
      Set PC = ThisWorkbook.PivotCaches.Create(SourceType:=xlExternal)
      Set PC.Recordset = RS
      Set PT = PC.CreatePivotTable(TableDestination:=ActiveSheet.Range(“A13”))
      to
      Set PC = ThisWorkbook.PivotCaches.Create(SourceType:=xlExternal, Version:=xlPivotTableVersion14)
      Set PC.Recordset = RS
      Set PT = PC.CreatePivotTable(TableDestination:=ActiveSheet.Range(“A13”))

  117. Hi,
    How could I modify this code so that it opens a single file (not through filepicker) and creates a pivot table from all the sheets in the workbook but on a new workbook?
    Any assistance would be greatly appreciated.
    Walter

Leave a Reply to Kirill Lapin (KL) Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.