Pivot tables - multiple consolidation ranges

ashleywanless

Board Regular
Joined
Aug 4, 2009
Messages
158
Hi,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p> </o:p>
I’m having issues with a pivot chart which has source data from multiple consolidation ranges. I have 4 sheets with data in them, 5 columns in total (A:E), all 4 sheets have exactly the same format, column A is Task, B is Time, C is Business Area, D is Administrator and E is date.
<o:p> </o:p>
Now I want to use a pivot to analyse this data but when I go through the multiple consolidation steps to produce the pivot the pivot table only shows row, column and value in the pivot table field list. Why doesn’t it show my column headers in this field? Is it possible to do this as I wish to analyse task times by business area.

Would i need to write a macro or use an excel function to collate the 4 sheets into one and run the pivot from this?

Thanks
<o:p> </o:p>
Thanks
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,797
Office Version
365
Platform
Windows
Hello

Multiple Consolidation ranges work differently to conventional pivot tables and have limitations. See here.

It is generally better to keep your data in a single table.

If you want to continue with data across sheets then this board has a plenty of examples of how to create a singular recordset from data across many tables. In particular look for posts by Fazza.

One way that I have used before, and seems to work ok and quickly provided your data tables are not too large (ie. not tens of thousands of rows)...

Code:
[COLOR=blue]Public[/COLOR] [COLOR=blue]Sub[/COLOR] ConsolidateAndPivot()
    [COLOR=blue]Dim[/COLOR] rngCell [COLOR=blue]As[/COLOR] Range, strRange [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR], rngData [COLOR=blue]As[/COLOR] Range
    [COLOR=blue]Dim[/COLOR] pvc [COLOR=blue]As[/COLOR] PivotCache, pvt [COLOR=blue]As[/COLOR] PivotTable
    [COLOR=blue]Dim[/COLOR] arrSheets [COLOR=blue]As[/COLOR] [COLOR=blue]Variant[/COLOR], wks [COLOR=blue]As[/COLOR] Worksheet
    [COLOR=blue]Dim[/COLOR] recData [COLOR=blue]As[/COLOR] ADOR.Recordset
 
    [COLOR=blue]Set[/COLOR] recData = [COLOR=blue]New[/COLOR] ADOR.Recordset
    arrSheets = [COLOR=blue]Array[/COLOR]("Sheet1", "Sheet2", "Sheet3")
    strRange = "A:E"
    [COLOR=blue]Set[/COLOR] pvt = Sheets(1).PivotTables(1)
 
    [COLOR=blue]For[/COLOR] [COLOR=blue]Each[/COLOR] wks [COLOR=blue]In[/COLOR] Sheets(arrSheets)
        [COLOR=blue]Set[/COLOR] rngData = Intersect(wks.Range(strRange), wks.UsedRange)
        [COLOR=blue]If[/COLOR] wks.Name = arrSheets(0) [COLOR=blue]Then[/COLOR]
            [COLOR=blue]For[/COLOR] [COLOR=blue]Each[/COLOR] rngCell [COLOR=blue]In[/COLOR] rngData.Resize(1)
                [COLOR=blue]With[/COLOR] recData
                    [COLOR=blue]With[/COLOR] .Fields
                        [COLOR=blue]Select[/COLOR] [COLOR=blue]Case[/COLOR] [COLOR=blue]TypeName[/COLOR](rngCell.Offset(1).Value)
                            [COLOR=blue]Case[/COLOR] "String"
                                .Append rngCell.Text, adVarChar, 255
                            [COLOR=blue]Case[/COLOR] "Date"
                                .Append rngCell.Text, adDate
                            [COLOR=blue]Case[/COLOR] "Double", "Currency"
                                .Append rngCell.Text, adDouble
                        [COLOR=blue]End[/COLOR] [COLOR=blue]Select[/COLOR]
                    [COLOR=blue]End[/COLOR] [COLOR=blue]With[/COLOR]
                [COLOR=blue]End[/COLOR] [COLOR=blue]With[/COLOR]
            [COLOR=blue]Next[/COLOR] rngCell
        [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR]
        [COLOR=blue]If[/COLOR] recData.State = 0 [COLOR=blue]Then[/COLOR] recData.Open
        [COLOR=blue]Set[/COLOR] rngData = Intersect(rngData.Offset(1), wks.UsedRange)
        [COLOR=blue]For[/COLOR] [COLOR=blue]Each[/COLOR] rngCell [COLOR=blue]In[/COLOR] rngData
            [COLOR=blue]With[/COLOR] recData
                [COLOR=blue]If[/COLOR] rngData(0, rngCell.Column).Text = .Fields(0).Name [COLOR=blue]Then[/COLOR] .AddNew
                .Fields(rngData(0, rngCell.Column).Text).Value = rngCell.Value
                .Update
            [COLOR=blue]End[/COLOR] [COLOR=blue]With[/COLOR]
        [COLOR=blue]Next[/COLOR] rngCell
    [COLOR=blue]Next[/COLOR] wks
 
    [COLOR=blue]With[/COLOR] Sheets.Add(Sheets(1))
        [COLOR=blue]Set[/COLOR] pvc = ThisWorkbook.PivotCaches.Add(SourceType:=xlExternal)
        [COLOR=blue]Set[/COLOR] pvc.Recordset = recData
        [COLOR=blue]With[/COLOR] pvc.CreatePivotTable(TableDestination:=.Range("A1"))
            pvt.CacheIndex = .CacheIndex
        [COLOR=blue]End[/COLOR] [COLOR=blue]With[/COLOR]
        Application.DisplayAlerts = [COLOR=blue]False[/COLOR]
            .Delete
        Application.DisplayAlerts = [COLOR=blue]True[/COLOR]
    [COLOR=blue]End[/COLOR] [COLOR=blue]With[/COLOR]
 
    recData.Close
    [COLOR=blue]Set[/COLOR] recData = [COLOR=blue]Nothing[/COLOR]
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]


Assumptions:
  1. You have a pivot table in your first sheet. If you don't already then create a blank pivot table. If you want it to point to a different existing pivot table then change this bit:
    Code:
    Set pvt = Sheets(1).PivotTables(1)
  2. Your data is spread across 3 sheets called Sheet1, Sheet2 and Sheet3. You are likely to have to change this bit:
    Code:
    arrSheets = Array("Sheet1", "Sheet2", "Sheet3")
  3. Each of those sheets is used to exclusively house that data (i.e. a single table, no other summaries beneath it.
  4. You have made reference to the Microsoft ActiveX Data Objects Recordset Library. You can do so by going Tools > References (in the VBE) and checking that library.
  5. Your data tablle has field names / column labels in row 1 in each sheet.
I have only ever attempted this using excel 2003.
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,797
Office Version
365
Platform
Windows
Missed the bit about you wanting to consolidate all the tables. If you are happy with that route then something like:

Code:
[COLOR="Blue"]Public[/COLOR] [COLOR="Blue"]Sub[/COLOR] ConsolidateTables()
    [COLOR="Blue"]Dim[/COLOR] arrSheets [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Variant[/COLOR]
    [COLOR="Blue"]Dim[/COLOR] strAddress [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR], rngData [COLOR="Blue"]As[/COLOR] Range, rngHeaders [COLOR="Blue"]As[/COLOR] Range
    [COLOR="Blue"]Dim[/COLOR] wks [COLOR="Blue"]As[/COLOR] Worksheet
    
    arrSheets = [COLOR="Blue"]Array[/COLOR]("Sheet1", "Sheet2", "Sheet3")
    strAddress = "A:E"
    
    [COLOR="Blue"]For[/COLOR] [COLOR="Blue"]Each[/COLOR] wks [COLOR="Blue"]In[/COLOR] Sheets(arrSheets)
        [COLOR="Blue"]If[/COLOR] wks.Name = arrSheets(0) [COLOR="Blue"]Then[/COLOR]
            [COLOR="Blue"]Set[/COLOR] rngHeaders = wks.Range(strAddress).Resize(1)
        [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]
        rngHeaders.Copy Destination:=Sheets(1).Range("A1")
        [COLOR="Blue"]Set[/COLOR] rngData = Intersect(wks.UsedRange, wks.Range(strAddress))
        rngData.Offset(1).Copy Destination:=Sheets(1).Range("A" & Rows.Count).End(xlUp)(2)
    [COLOR="Blue"]Next[/COLOR] wks
    
    Application.CutCopyMode = [COLOR="Blue"]False[/COLOR]
    
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]
Assumptions:
  1. You have data in 3 sheets called Sheet1, Sheet2 and Sheet3. Change this bit:
    Code:
    arrSheets = Array("Sheet1", "Sheet2", "Sheet3")
  2. You have row headers / column labels in Row 1 in each of those sheets.
  3. You want to paste the data into the 1st sheet in the workbook. Change:
    Code:
    rngHeaders.Copy Destination:=Sheets([B]1[/B]).Range("A1")
    and:
    Code:
    rngData.Offset(1).Copy Destination:=Sheets([B]1[/B]).Range("A" & Rows.Count).End(xlUp)(2)
 

ashleywanless

Board Regular
Joined
Aug 4, 2009
Messages
158
thanks for youre code by referring to adresses rather than lines it runs so much quicker. My only issue though is the data im copying from is links to another workbook, therefore when its pasted i would like is pasted as values. Is this possible?

Thanks so much
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,797
Office Version
365
Platform
Windows
Sure, change:
Code:
rngData.Offset(1).Copy Destination:=Sheets(1).Range("A" & Rows.Count).End(xlUp)(2)
to:
Code:
rngData.Offset(1).Copy
Sheets(1).Range("A" & Rows.Count).End(xlUp)(2).PasteSpecial xlValues
 

ashleywanless

Board Regular
Joined
Aug 4, 2009
Messages
158
Hi,

Ok i get a compile error which highlights the xl values line in you're code. I have tried to do this copying and pasting before as values and always get stuck.

Thanks
 

ashleywanless

Board Regular
Joined
Aug 4, 2009
Messages
158
Sorry the error i receive is compile error - expected array

Also can i ask what the (2) mean in the line of code you provided refer too? are these sheet numbers?
Thanks
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,797
Office Version
365
Platform
Windows
It was a little cheat to attempt to offset from the last row to the one below, try;

Code:
Sheets(1).Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlValues</pre>
 

ashleywanless

Board Regular
Joined
Aug 4, 2009
Messages
158
Ok i have tried the above method and it doesnt work for me because of the way i have linked my 4 'source' workbooks to the master, think this means i cant use the address to copy data.

I have 4 source workbooks which contain the actual data. I have then created a master workbook which has 5 sheets, 4 to replicate the source workbook sheets and 1 "data" sheet which i want to stack all records in. The 4 sheets in my master workbook use if statements to link to the source books, these if statements only pulls accross data so if there is a blank the worksheet shows a blank. i.e if(sheet1'A1="","",sheet1'A1) this means i dont get 0 where data doesnt exist.

Each of my 4 replicated sheets in my master have these if statements in range A1:E10000 so that all future data input will feed straight through.

Maybe i am making the above part harder than needs be? because of this i cant refer to address in any of my 4 sheets in the master as it always copies the blank data down to row 10000. I think it also confuses the final row counts as it thinks there is data all the way down to row 10000.

Would it be possible for me to write some code which works through all 4 of my sheets but copies data to the master based on columns a:e in a row containing data? also any ideas on how can set the final row without referring to xlup?

Please let me know if you think there is a better way of linking my master to the source workbooks. I did try this without the if statement but just got loads of 0 where data didnt exist. I need to be able to also use the master to create a record stack while users have the source workbooks open which wasnt a problem when i went down the if statement route.

Any help/advice greatly appreciated
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,797
Office Version
365
Platform
Windows
Hi

I don't think pulling the data in with formula is a very good idea. You are likely to increase the file size and calculation time and you run the risk of not pulling in everything if the data range exceeds the rows that your formula occupies.

What version of excel are you using?

I would recommend that you Import your data. In each sheetyou would go to Data > Import, find the file you want, choose the relevant sheet and then import all the data. This could leave you with a permanent query table back to the source file so all you need to do is refresh the table (i.e. right-click the table > refresh).

Then you could alter the existing macro to:

Code:
[COLOR="Blue"]Public[/COLOR] [COLOR="Blue"]Sub[/COLOR] ConsolidateTables()
    [COLOR="Blue"]Dim[/COLOR] arrSheets [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Variant[/COLOR]
    [COLOR="Blue"]Dim[/COLOR] strAddress [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR], rngData [COLOR="Blue"]As[/COLOR] Range, rngHeaders [COLOR="Blue"]As[/COLOR] Range
    [COLOR="Blue"]Dim[/COLOR] wks [COLOR="Blue"]As[/COLOR] Worksheet
    
    arrSheets = [COLOR="Blue"]Array[/COLOR]("Sheet1", "Sheet2", "Sheet3")
    strAddress = "A:E"
    
    [COLOR="Blue"]With[/COLOR] Application
        .Calculation = -4135
        .ScreenUpdating = [COLOR="Blue"]False[/COLOR]
        .EnableEvents = [COLOR="Blue"]False[/COLOR]
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
    
    [COLOR="Blue"]For[/COLOR] [COLOR="Blue"]Each[/COLOR] wks [COLOR="Blue"]In[/COLOR] Sheets(arrSheets)
        wks.UsedRange
        [COLOR="Blue"]If[/COLOR] wks.Name = arrSheets(0) [COLOR="Blue"]Then[/COLOR]
            [COLOR="Blue"]Set[/COLOR] rngHeaders = wks.Range(strAddress).Resize(1)
        [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]
        rngHeaders.Copy Destination:=Sheets(1).Range("A1")
        [COLOR="Blue"]Set[/COLOR] rngData = Intersect(wks.UsedRange, wks.Range(strAddress))
        rngData.Offset(1).Copy
        Sheets(1).Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial -4163
    [COLOR="Blue"]Next[/COLOR] wks
    
    [COLOR="Blue"]With[/COLOR] Application
        .CutCopyMode = [COLOR="Blue"]False[/COLOR]
        .ScreenUpdating = [COLOR="Blue"]True[/COLOR]
        .Calculation = -4105
        .EnableEvents = [COLOR="Blue"]True[/COLOR]
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]
 

Watch MrExcel Video

Forum statistics

Threads
1,095,929
Messages
5,447,361
Members
405,447
Latest member
WPY

This Week's Hot Topics

Top