Page 1 of 7 123 ... LastLast
Results 1 to 10 of 70

Thread: Creating a pivot table with multiple sheets
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2008
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Creating a pivot table with multiple sheets

    Hi
    I am trying to create a pivot table using multiple sheets. I looking for some code that will replace the "consolidated ranges" in the Pivot Table Wizard. I am looking for code because I am writing a macro that will create a different number of worksheets in the Workfile, depending on which dataset I use. i.e File 1 may have 75 worksheets, whereas File 2 may have 120 worksheets. '

    The ranges on each of the worksheets will be the same. Range("A2:Av48")

    Any help or directions to other references will be gratefully received.

    Thanks

  2. #2
    MrExcel MVP
    Int'l Moderator
    Greg Truby's Avatar
    Join Date
    Jun 2002
    Location
    39 17' 15" N, -94 40' 26" W
    Posts
    9,998
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Creating a pivot table with multiple sheets

    Welcome to the board.

    You have discovered what, to me, is one of the most annoying weaknesses of PivotTables. I have probably done a few hundred of them (I do at least two or three a week). And I have NEVER, NOT ONCE found a use for the ruddy "consolodated ranges" nonsense. You can fiddle around with it a bit - maybe, just maybe - it will do what you need.

    My guess is that you will probably have to copy and paste all of your data onto one worksheet. And if you are unable to filter your raw data down to less than 65K rows (if you are using XL2003 or lower) you will have to push the data out to Access and hook back in using MS Query or the like.

    Sorry to be the bearer of bad tidings.
    Greg

    Home: XL 2003, 2007, 2010, and 2013 on Windows 7
    Work: XL 2013, 2016 on Windows 10
    Please use CODE tags - especially for longer excerpts of code.

  3. #3
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    9,021
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Creating a pivot table with multiple sheets

    It isn't clear if the range you gave includes the headers or not. I assume as you have started with row 2, that the headers are in row 1. If not, please advise it is a simple change.

    Assumes your data file is active - such as if you put this in an add-in. (It need not be active, BTW, in fact you could write something to work on closed files if needed.)

    If you advise the field names & PT setup - row & column fields - the steps to actually make the pivot table can be added. It is just a couple of lines.

    And, the data file must have been saved. There will be an error otherwise. I haven't put in error checks - just quickly put this together. AND, it is assumed that every worksheet has data.

    regards, Fazza

    Code:
    Sub test()
    
      Dim i As Long
      Dim arSQL() As String
      Dim objPivotCache As PivotCache
      Dim objRS As Object
      Dim wbkNew As Workbook
      Dim wks As Worksheet
    
      With ActiveWorkbook
        ReDim arSQL(1 To .Worksheets.Count)
        For Each wks In .Worksheets
          i = i + 1
          arSQL(i) = "SELECT * FROM [" & wks.Name & "$]"
        Next wks
        Set wks = Nothing
        Set objRS = CreateObject("ADODB.Recordset")
    
        objRS.Open Join$(arSQL, " UNION ALL "), Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _
            .FullName, ";Extended Properties=""Excel 8.0;"""), vbNullString)
      End With
    
      Set wbkNew = Workbooks.Add(Template:=xlWBATWorksheet)
    
      With wbkNew
        Set objPivotCache = .PivotCaches.Add(xlExternal)
        Set objPivotCache.Recordset = objRS
        Set objRS = Nothing
    
        With .Worksheets(1)
          objPivotCache.CreatePivotTable TableDestination:=.Range("A3")
          Set objPivotCache = Nothing
        End With
      End With
      Set wbkNew = Nothing
    End Sub

  4. #4
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    9,021
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Creating a pivot table with multiple sheets

    If the data range "A2:AV48" includes headers, change from
    Code:
    arSQL(i) = "SELECT * FROM [" & wks.Name & "$]"
    to
    Code:
    arSQL(i) = "SELECT * FROM [" & wks.Name & "$A2:AV48]"

  5. #5
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    9,021
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Creating a pivot table with multiple sheets

    to make the pivot table, change from
    Code:
    Set objPivotCache = Nothing
    to something like, modify to suit,
    Code:
    Set objPivotCache = Nothing
    
          With .PivotTables(1)
            .PivotFields("Company").Orientation = xlPageField
            .PivotFields("Department").Orientation = xlRowField
            .PivotFields("Year").Orientation = xlColumnField
            .PivotFields("Cost").Orientation = xlDataField
          End With
    Untested.

  6. #6
    New Member
    Join Date
    Apr 2008
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Creating a pivot table with multiple sheets

    Hi Fazza

    Thank you for your feedback.

    The data comes from one sheet, named "Data"
    I have saved the workfile as "pivotmacro"

    There are ten age categories and a variable number of second categories. So if there are three second categories I will have a total of 30 sheets.

    Names of sheets:
    The names of each sheet can be found in cell c1 of that particular sheet. I have also set up a loop that puts the name of each created sheet onto the data worksheet.

    The range of sheet names will be dependent on how many sheets there are, but they will fall within N:X on the data worksheet. If I use three sub categories it will fall between A1 to X3

    Headers and rows
    The header categories are in row2 and the rows are named in column A for all the sheets

    ------------------------------------------------------------------------
    I have tried to run your macro, but there is an error that says incorrect query clause. When I debug the following is highlighted:

    objRS.Open Join$(arSQL, " UNION ALL "), Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _
    .FullName, ";Extended Properties=""Excel 8.0;"""), vbNullString)

    Another query I have is whether it is important that the worksheets come after or before the "data" worksheet

    Thanks for your assistance

  7. #7
    MrExcel MVP
    Int'l Moderator
    Greg Truby's Avatar
    Join Date
    Jun 2002
    Location
    39 17' 15" N, -94 40' 26" W
    Posts
    9,998
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Creating a pivot table with multiple sheets

    Fazza,

    I'm looking forward to testing this tomorrow at work. Am I reading this correctly?! Can we create a pivotcache that will be stored in the workbook; the cache being based on a recordset that could conceivably (a) exceed 65,000 rows and (b) not have to be stored in Access?
    Greg

    Home: XL 2003, 2007, 2010, and 2013 on Windows 7
    Work: XL 2013, 2016 on Windows 10
    Please use CODE tags - especially for longer excerpts of code.

  8. #8
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    9,021
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Creating a pivot table with multiple sheets

    Quote Originally Posted by Greg Truby View Post
    Fazza,

    I'm looking forward to testing this tomorrow at work. Am I reading this correctly?! Can we create a pivotcache that will be stored in the workbook; the cache being based on a recordset that could conceivably (a) exceed 65,000 rows and (b) not have to be stored in Access?
    Sure can, Greg. And with the power of SQL it can do a bit more work on the way if required.

    And it could be pulling data from closed files or mutliple files. And it is fast. I've been learning to use this over the last year or so and am still impressed about how good it is.

    best regards, Fazza

  9. #9
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    9,021
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Creating a pivot table with multiple sheets

    Quote Originally Posted by xlsaffer View Post
    Hi Fazza

    Thank you for your feedback.

    The data comes from one sheet, named "Data"
    I have saved the workfile as "pivotmacro"

    There are ten age categories and a variable number of second categories. So if there are three second categories I will have a total of 30 sheets.

    Names of sheets:
    The names of each sheet can be found in cell c1 of that particular sheet. I have also set up a loop that puts the name of each created sheet onto the data worksheet.

    The range of sheet names will be dependent on how many sheets there are, but they will fall within N:X on the data worksheet. If I use three sub categories it will fall between A1 to X3

    Headers and rows
    The header categories are in row2 and the rows are named in column A for all the sheets

    ------------------------------------------------------------------------
    I have tried to run your macro, but there is an error that says incorrect query clause. When I debug the following is highlighted:

    objRS.Open Join$(arSQL, " UNION ALL "), Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _
    .FullName, ";Extended Properties=""Excel 8.0;"""), vbNullString)

    Another query I have is whether it is important that the worksheets come after or before the "data" worksheet

    Thanks for your assistance
    My initial reading of the above has confused me.

    Your initial post described, I thought, having data files with many worksheets. So one data file with say 75 or 120 worksheets. That is what I have coded for.

    Now you write the data comes from one sheet? And the names of sheets are in cell C1. And a loop to put the sheet names on the data sheet. This is a different setup and it isn't at all clear.

    To test the code, simply set up one workbook with data on every worksheet. Suggest you make it simple with row 1 headers and data below that from row 2. The same headers in every worksheet. The code should work on such a (saved at some time) file. For a set up like described in your latest post it won't.

    HTH, Fazza

  10. #10
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    9,021
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Creating a pivot table with multiple sheets

    PS
    Greg, The SQL string can be 65,000+ characters! Fazza

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •