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

 

[Fazza]

some more information

As this thread is viewed a lot, I thought it was was worth adding some additional comments.

Initially the topic was code to make a pivot table from all the worksheets in a single workbook. Later there is code to create a pivot table from multiple workbooks.

Code isn't required for these tasks, btw. The basic approach being used to create a pivot table from multiple sources uses SQL to define the data source.

The basics of SQL.

Basic SQL is

SELECT *
FROM table

Where the "*" indicates that all fields are selected. The alternative is to identify the fields required,

SELECT field1, field2, field3
FROM table

Fields can be in any order in the source document. If using the "SELECT *" approach on multiple tables the fields must be in the same order in every table. If listing the field names specifically - that is, not using "SELECT *" - the order of the fields in the source tables does not matter. When querying Excel files, some further details are

'how to handle field names that contain spaces
SELECT `field name containing a space`, FieldNameWithoutSpace
 
'how to rename fields
SELECT source_name AS [New Name]
 
'how to use functions - many VBA functions can be used if using Excel databases
'this example enables fields that are not in the source data to be created
SELECT IIF(`some field name`>0,'Positive', 'Negative') AS [MyNewField]

FOr combining multiple source it can be

SELECT field1, field2
FROM table1
UNION ALL
SELECT field1, field2
FROM table2
UNION ALL
SELECT field1, field2
FROM table3

Criterea can be added

SELECT field1, field2
FROM table
WHERE field3 Is Null AND field2 <> 123 AND field4 = 'Positive'

If using SUM or other aggregate functions,

SELECT field1, field2, SUM(field3) AS [MySum], field4
FROM table
WHERE field1 = 123
GROUP BY field1, field2, field4
HAVING SUM(field3) > 1000

Data can be joined from multiple tables. This also shows using a table alias

SELECT A.field1, A.field2, B.anotherfield
FROM onetable A, anothertable B
WHERE A.ID = B.ID

Where I've used field1 and field2, etc these are dummy values for the real field names. Actually using field1 as a name will only work if you have a field called field1. If you have data with no headers, you might be able to use F1 and F2, etc to refer to the fields.

A google search will reveal many sites for learning SQL. Here is a good starting point http://www.w3schools.com/sql/default.asp

The `table` name can be a worksheet name (which assumes the data is in the first row and data immediately under) or a fixed range or a named range. Dynamic named ranges are not usable.

Information on closely related subjects, including using ADO,
http://support.microsoft.com/kb/257819
http://www.xtremevbtalk.com/showthread.php?t=217783
http://excelkb.com/
http://support.microsoft.com/kb/257819
http://support.microsoft.com/kb/257819
http://support.microsoft.com/default.aspx?scid=kb;en-us;q319998
http://www.connectionstrings.com/default.aspx
http://msdn.microsoft.com/en-us/library/ms966772.aspx
http://support.microsoft.com/kb/303814
http://www.dailydoseofexcel.com/archives/2004/12/13/
http://www.dailydoseofexcel.com/archives/2004/06/03/external-data-mixed-data-types/

Here is the non-code approach. As above, worksheet names can be used however I will assume defined names are used for the source data. Such as say there are four worksheets "North", "South", "East" & "West". The data (including header) have defined names tbl1, tbl2, tbl3 & tbl4. The defined names are 'normal' defined names, not dynamic names. Safest (to avoid memory leak when creating pivot tables) is to save the file and from a new file take menu path data, import external data, new database query (ALT-D-D-N), OR, start the pivot table wizard and take the external data option at the first step. (If defined names have not been used and are not in the source file you might get an error message about no visible tables. OK to this and then hit 'options' and then 'system tables' & 'OK'. By selecting system tables the worksheet names should be available. This will only be helpful if the first worksheet row contains headers and the data is directly under. The described use of defined names is a surer approach. ) Follow the wizard and take the option to edit the SQL in MS Query at the final step. Within MS Query, hit the 'SQL' icon and then edit the SQL to be like, if all the tables have the same fields in the same order and you want to 'pick up' all the fields,

SELECT *
FROM tbl1
UNION ALL
SELECT *
FROM tbl2
UNION ALL
SELECT *
FROM tbl3
UNION ALL
SELECT *
FROM tbl4

If you don't want all the fields or the fields are not in the same order in all tables it might be like

SELECT field1, field2, field3
FROM tbl1
UNION ALL
SELECT field1, field2, field3
FROM tbl2
UNION ALL
SELECT field1, field2, field3
FROM tbl3
UNION ALL
SELECT field1, field2, field3
FROM tbl4

Hit the SQL icon again - and see the results set on screen - and then hit the 'open door' icon to exit MS Query and then complete the pivot table. (BTW, if you started via the ALT-D-D-N option you can instead create a query table. These have their own idiosynchrosies, btw.) The resultant worksheet can be moved into the source file or left in a separate file. The SQL is only text. So it can be generated in a scratch worksheet if you like. Just set up some text and a few formulas to concatenate it together. This allows long strings to be easily created. There is no need for separate lines within the SQL as I've shown, a space is OK. It is simple text, though with strict syntax for the SQL to work correctly.

The VBA code was used earlier in the thread because the OP was after a code solution. As just described, code is not required.

 

[ThomasB]

pivot table with multiple sheets not picking up values

Hi Fazza

Thank you for your multiple sheet pivot table consolidation code which I gleaned from Bill Jelens Excel Gurus Gone Wild have modified it to create 2 all singing and dancing pivot tables, the 2 pivots are created in the same workbook the data sheets are in which are the 12 month Jan to Dec.

The code work perfectly but for some bizzare reason, I cant seem to pick up a some material numbers on 3 of the months the other months all work perfectly, I have checked the formatting is the same but no different and am well and truly stumped as the SQL statment should pick up all data, was wondering if you know of any reason why the code would not pickup certain values?

In the code below the values which are not pickup up in the 3 months belong to
.PivotFields("MRDR CODE").Orientation = xlRowField

Any help would be most appreciated
Kind Regards
Thomas



Code Below:

Sub bob()
Dim i As Long
Dim arSQL() As String
Dim objPivotCache As PivotCache
Dim objRS As Object
Dim wks As Worksheet
Dim ws2 As Worksheet

With ActiveWorkbook
On Error Resume Next
Application.DisplayAlerts = False
Sheets("New Report").Delete
Application.DisplayAlerts = True
On Error GoTo 0

ReDim arSQL(1 To 12)
For Each wks In .Worksheets
Select Case wks.Name
Case "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"
i = i + 1
arSQL(i) = "SELECT * FROM [" & wks.Name & "$A3:AB65536]"
End Select
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)
Set objPivotCache = .PivotCaches.Add(xlExternal)
Set objPivotCache.Recordset = objRS
Set objRS = Nothing
End With

Worksheets.Add(after:=Worksheets("Dec")).Name = "New Report"
Set ws2 = Worksheets("New Report")

With ws2
objPivotCache.CreatePivotTable TableDestination:=Range("A9")
Set objPivotCache = Nothing
With .PivotTables(1)

.PivotFields("BU").Orientation = xlPageField
.PivotFields("Month").Orientation = xlPageField
.PivotFields("Year").Orientation = xlPageField

.PivotFields("-").Orientation = xlDataField


.PivotFields("Activity").Orientation = xlRowField
.PivotFields("Brand").Orientation = xlRowField
.PivotFields("MRDR CODE").Orientation = xlRowField
.PivotFields("Product Description (this f").Orientation = xlRowField
.PivotFields("Status").Orientation = xlRowField
.PivotFields("Comments").Orientation = xlRowField
.PivotFields("Pack Size").Orientation = xlRowField
.PivotFields("OLD MRDR CODE").Orientation = xlRowField

.PivotFields("Activity").Subtotals(1) = True
.PivotFields("Brand").Subtotals(1) = True
.PivotFields("MRDR CODE").Subtotals(1) = True
.PivotFields("Product Description (this f").Subtotals(1) = True
.PivotFields("Status").Subtotals(1) = True
.PivotFields("Comments").Subtotals(1) = True
.PivotFields("Pack Size").Subtotals(1) = True
.PivotFields("OLD MRDR CODE").Subtotals(1) = True
.PivotFields("Activity").Subtotals(1) = False
.PivotFields("Brand").Subtotals(1) = False
.PivotFields("MRDR CODE").Subtotals(1) = False
.PivotFields("Product Description (this f").Subtotals(1) = False
.PivotFields("Status").Subtotals(1) = False
.PivotFields("Comments").Subtotals(1) = False
.PivotFields("Pack Size").Subtotals(1) = False
.PivotFields("OLD MRDR CODE").Subtotals(1) = False

.PivotSelect "Activity[Flowthroughs]", xlLabelOnly
Selection.Interior.ColorIndex = 35
.PivotSelect "Activity[Price Promotion]", xlLabelOnly
Selection.Interior.ColorIndex = 37
.PivotSelect "Activity[Special Packs]", xlLabelOnly
Selection.Interior.ColorIndex = 36
.PivotSelect "Activity[NPD]", xlLabelOnly
Selection.Interior.ColorIndex = 43
.PivotSelect "Activity[Delisting]", xlLabelOnly
Selection.Interior.ColorIndex = 38

.PivotFields("Year").CurrentPage = 2009
.PivotFields("Month").CurrentPage = "May"
.PivotFields("BU").CurrentPage = "Foods"

ActiveWorkbook.Sheets("New Report").Range("B5:B7").Interior.ColorIndex = 6
ActiveWorkbook.Sheets("New Report").Tab.ColorIndex = 39
ActiveWorkbook.ShowPivotTableFieldList = False
Application.CommandBars("Stop Recording").Visible = False
Application.CommandBars("PivotTable").Visible = False
End With
ActiveWindow.Zoom = 85
Range("A3").Select
End With

CopyDetailsReport
End Sub

 

[Fazza]

Re: pivot table with multiple sheets not picking up values

Hi, Thomas.

Most likely mixed data types.

http://support.microsoft.com/default.aspx?scid=kb;en-us;257819
http://www.dailydoseofexcel.com/archives/2004/06/03/external-data-mixed-data-types/

So, all will be OK if a field has (in every record) entirely numeric entries and no text. Equally, all will be OK if a field has entirely text entries but no numeric entries. However if they are mixed (some numeric records and some text), you can expect that all of one type will be returned OK and the different ones will be blank. Does that fit your situation?

By the way, good to see you have used

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

As this will ensure that the full data is detected. I am wary of using simply a worksheet name reference - FROM [Jan$] - and using FROM [Jan$A3:AB65536] is good. It might however pick up many, many records of blank rows so you might consider excluding them with a criteria such as

WHERE YourFieldName Is Not Null

So the full line might be like

arSQL(i) = "SELECT * FROM [" & wks.Name & "$A3:AB65536] WHERE Account Is Not Null"

HTH, Fazza

 

[ThomasB]

Hi Fazza

Thank you very much for your help it proved to be most helpfull

I initially tried to format the MRDR Code columns as text and then as General and then as Number and the numbers still did not appear in 3 of the months, however using one of the links you gave me was able to solve the problem in fact so well I think (correct me if i'm wrong?) I can now used mixed data types and they will all appear!!

http://www.dailydoseofexcel.com/arch...ed-data-types/

quote from link

"There is one final twist. A setting of IMEX=1 in the connection
string’s extended property determines whether the ImportMixedTypes
value is honored. IMEX refers to IMport EXport mode. There are three
possible values. IMEX=0 and IMEX=2 result in ImportMixedTypes being
ignored and the default value of ‘Majority Types’ is used. IMEX=1 is
the only way to ensure ImportMixedTypes=Text is honored. The resulting
connection string might look like this:"

Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:\ db.xls;
Extended Properties=’Excel 8.0;HDR=Yes;IMEX=1′


basically I needed to change in my code:

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

To

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

i.e. add in HDR=Yes;IMEX=1

it now works a treat, the boss is going to be most impressed!!

One final question, when the pivot cache is generated from the consolidated worsheets, is it is stored in the Workbook or Worksheet in question? (ie my code creates 2 Pivots in 2 Worksheets in the same Workbook as the data)

When I delete the worksheets containing these pivots is the pivot cache cleared?

ie using:

Sheets("New Report").Delete (This sheet has the pivot displayed on it)

or is the pivot cache still stored in Workbook and I need to use some code to clear it?

Kind Regards

Thomas

 

[Fazza]

I suspect you will find that it (IMEX) is not a robust solution. Here is another thread that is similar, btw. http://www.mrexcel.com/forum/showthread.php?t=286480

Re, the pivotcache. AFAIK it is associated with the workbook. I guess if there are no worksheets, the pivot cache would be cleared at that time.

regards, Fazza

 

[Fazza]

For reference, some older threads that have simple examples of creating pivot tables from mutliple sources.

http://www.mrexcel.com/forum/showthread.php?t=204801

http://www.mrexcel.com/forum/showthread.php?t=205831

http://www.mrexcel.com/forum/showthread.php?t=209763

http://www.mrexcel.com/forum/showthread.php?t=237348

http://www.mrexcel.com/forum/showthread.php?t=239043

 

[Matty]

Hello Fazza,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Here's the situation I have and I’m wondering whether you can provide a solution:<o></o>
<o> </o>
I have two separate Worksheets within the same Workbook, each containing data laid out in the exact same order with headers in Row 1 and data beneath.<o></o>
<o> </o>
I then have a Pivot Table on a third Worksheet that I want to display the data held in Worksheets 1 and 2.<o></o>
<o> </o>
I have already tried creating a simple UNION query in MS Query to bring the data together from Worksheets 1 and 2 together – and this initially worked well – but it requires static Named Ranges to be used rather than dynamic ones, and as my data increases/decreases from one week to the next, a static Named Range isn’t viable. Plus, if I try to go back into the query post creation, I get an ODBC Login Failure! <o></o>
<o> </o>
I have tried out your code for creating a Pivot Table based on multiple Worksheets in the past, and it works really well. But in this situation I don’t want a new Pivot Table created each and every time;, rather I want to use the Pivot Table already in existence (the main reason being that it has some Conditional Formatting applied to it that I do not want to lose).<o></o>
<o> </o>
Can you set me off in the right direction in terms of how I would go about doing what I'm after, please?<o></o>
<o> </o>
Thanks a lot.<o></o>
<o> </o>
Matty<o></o>

 

[Fazza]

Hi, Matty.

Assuming the pivot table is set up OK, what about using VBA to update the named ranges? Such as on sheet deactivation. Right click from tab of the first worksheet with data and take the view code option. Then enter code like,

Private Sub Worksheet_Deactivate()
  Me.Range("A1").CurrentRegion.Name = "table_1"
End Sub

And from the other data worksheet right click and again view code and enter

Private Sub Worksheet_Deactivate()
  Me.Range("A1").CurrentRegion.Name = "table_2"
End Sub

Change "table_1" and "table_2" to suit the names you have used. The code should update the named ranges every time there is deactivation of a data worksheet.

The only other step is to refresh the pivot table. That could be done manually, or as there is code already it would be handy to use code. Say a little subroutine, called after the line that updates the named ranges. Or maybe just put in the worksheet activation event code on the worksheet containing the pivot table.

sub refreshPT()
 with CodeNameOfWksWithPT.pivottables(1).pivotcache
  .missingitemslimit = xlmissingitemsnone
  .refresh
 end with
end sub

Or the range names could be updated in the subroutine, not in worksheet deactivation event code. Such as this single bit of code to replace all of the above. Just be sure to run it as required to refresh the PT.

sub refreshPT()
 
 CodeNameFirstDataSheet.range("A1").currentregion.name = "table_1"
 
 CodeNameSecondDataWks.range("A1").currentregion.name = "table_2"
 
 with CodeNameOfWksWithPT.pivottables(1).pivotcache
  .missingitemslimit = xlmissingitemsnone
  .refresh
 end with
 
end sub

regards, Fazza

 

[Matty]

Hello Fazza,

Many thanks for your response.

I have done as you've suggested and everything's working well apart from when I come to refresh the Pivot Table, when I get the following message:

Run-time error '1004'. [Microsoft][ODBC Excel Driver] The connection for viewing your linked Microsoft Excel worksheet was lost.

Any idea what could be wrong? I've perused the Help files but nothing's jumping out...

Thanks a lot.

Matty

 

[Fazza]

I don't know, Matty. Not familiar to me.

How was the pivot table created? What version of Excel? Are the data and pivot table in the same file. I assume it is not a shared file, btw.

Some ideas. Google for help. Create the pivot table afresh and see if that helps. Create the pivot table each time it is needed. If suitable, switch instead to a query table, or maybe preferable an ADO query - though this does not produce a pivot table so does not have the dynamic power of a pivot table: it is static. Separate the pivot table workbook from the data workbook.

HTH, Fazza