Creating a pivot table with multiple sheets

[xlsaffer]

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

 

[Greg Truby]

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.

 

[Fazza]

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

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

 

[Fazza]

If the data range "A2:AV48" includes headers, change from

arSQL(i) = "SELECT * FROM [" & wks.Name & "$]"

to

arSQL(i) = "SELECT * FROM [" & wks.Name & "$A2:AV48]"

 

[Fazza]

to make the pivot table, change from

Set objPivotCache = Nothing

to something like, modify to suit,

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.

 

[xlsaffer]

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

 

[Greg Truby]

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?

 

[Fazza]

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

 

[Fazza]

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

 

[Fazza]

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