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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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.
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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>
 
Upvote 0
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
 
Upvote 0
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]
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

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