Creating a pivot table with multiple sheets

X

xlsaffer

New Member
Joined
Apr 18, 2008
Messages
5
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
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
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.
 
Upvote 0
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
 
Upvote 0
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]"
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
Greg Truby said:
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?
Click to expand...

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
 
Upvote 0
xlsaffer said:
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
Click to expand...

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
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
VBA/Macro: Adding Pivot table with connection string
Replies
1
Views
182
Jette3173
J
K
  • Question Question
How to create pivot table that shows the delta between two other pivot tables?
Replies
2
Views
509
Kelvinator1
K
C
  • Question Question
VBA Consolidating Multiple Sheets in a Pivot Table
Replies
1
Views
313
c_clark
C
Y
  • Question Question
Pivot Table deletes on opening of workbook
Replies
3
Views
231
cooleomter
C
W
  • Question Question
Macro/VBA code code for creating new excel file for each pivot table from a column filter
Replies
1
Views
444
Anthony47
A

Forum statistics

Threads
1,223,924
Messages
6,175,416
Members
452,640
Latest member
steveridge

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top