Create updatable Pivot Table from multiple Worksheets in Excel

[abjectpoet]

Hi there,

I had a quick look through your FAQ and related subjects and couldn't find what I was looking for. I am a bit pressed for time, so please excuse me if I have missed an existing resource.

Before I explain my problem, a caveat: I am more of an end-user than a developer/programmer. I have limited programming experience and have turned to VBA as it seems the last possibility for solving my dilemma.

Here it is then: I have an Excel spreadsheet that details my organization's critical services, broken down into 14 Worksheets for each division. Each Worksheet contains the exact same table, which each division then populated with their data. I have been asked to consolidate this data on a single pivot table which can be updated as revisions are made to the source data on any Worksheet. I cannot introduce additional software, such as Access or another dedicated database, so I am stuck with an Excel-specific solution.

I quickly discovered that I can not use the Wizard's "Multiple Consolidation Ranges" tool because it spits out the wrong categories (i.e. Row and Column fields). As a workaround, I assigned each Division's table to a named range and fed them into MS Query, creating a New Database from Excel Files data source. I used some basic SQL to select and concatenate all named ranges and then returned the data to Excel as a Pivot.

Great! Except...it wouldn't refresh, making it useless. After doing some internet research, it seemed my problem was that the pivot table was refreshing faster than MS Query could digest the changes to the source data (which contains huge amounts of data). Apparently, the only way around this processing difference was to force MS Query to finish its work before refreshing the data. I would need to design some VBA code to orchestrate the order of processes.

This is where I need help. I've tried a number of different approaches that I found online but my foundational knowledge isn't strong enough at this point and I don't have the time to continue this fruitless trial and error.

In case you need to know, here is the SQL code I used to generate the pivot table:

SELECT * FROM QuebecTable QuebecTable
UNION ALL
SELECT * FROM OntarioTable OntarioTable
UNION ALL
SELECT * FROM NorthernTable NorthernTable
UNION ALL
SELECT * FROM AtlanticTable AtlanticTable
UNION ALL
SELECT * FROM AlbManSaskTable AlbManSaskTable
UNION ALL
SELECT * FROM AABTable AABTable
UNION ALL
SELECT * FROM CFOBTable CFOBTable
UNION ALL
SELECT * FROM CSBTable CSBTable
UNION ALL
SELECT * FROM DMOTable DMOTable
UNION ALL
SELECT * FROM HECSBTable HECSBTable
UNION ALL
SELECT * FROM HPFBTable HPFBTable
UNION ALL
SELECT * FROM LegalTable LegalTable
UNION ALL
SELECT * FROM PACCBTable PACCBTable
UNION ALL
SELECT * FROM SPBTable SPBTable

And here is the VBA code I have been working with. I know it is basic but attempts at streamlining some of the recursive elements haven't been successful, so I have aimed for "basic but functional":

Sub Refresh_Data()

Dim RPB_Q As Worksheet
Dim RPB_O As Worksheet
Dim RPB_N As Worksheet
Dim RPB_BAM As Worksheet
Dim RPB_A As Worksheet
Dim CSB As Worksheet
Dim SPB As Worksheet
Dim PAC As Worksheet
Dim LEG As Worksheet
Dim HEC As Worksheet
Dim HPF As Worksheet
Dim DMO As Worksheet
Dim CFB As Worksheet
Dim AAB As Worksheet
Dim Workshts(13)

With ThisWorkbook
Set RPB_Q = .Worksheets("RAPB-Quebec")
Set RPB_O = .Worksheets("RAPB-Ontario")
Set RPB_N = .Worksheets("RAPB-Northern")
Set RPB_BAM = .Worksheets("RAPB-NCR-BC-AB-MAN")
Set RPB_A = .Worksheets("RAPB-ATL")
Set CSB = .Worksheets("CSB")
Set SPB = .Worksheets("SPB")
Set PAC = .Worksheets("PACCB")
Set LEG = .Worksheets("LEGAL")
Set HEC = .Worksheets("HECSB")
Set HPF = .Worksheets("HPFB")
Set DMO = .Worksheets("DMO ASSOC")
Set CFB = .Worksheets("CFOB")
Set AAB = .Worksheets("AAB")
Set WrkShts(0) = RPB_Q
Set WrkShts(1) = RPB_O
Set WrkShts(2) = RPB_N
Set WrkShts(3) = RPB_BAM
Set WrkShts(4) = RPB_A
Set WrkShts(5) = CSB
Set WrkShts(6) = SPB
Set WrkShts(7) = PAC
Set WrkShts(8) = LEG
Set WrkShts(9) = HEC
Set WrkShts(10) = HPF
Set WrkShts(11) = DMO
Set WrkShts(12) = CFB
Set WrkShts(13) = AAB
End With

For i = 0 To 13
With WrkShts(i).QueryTables(1)
.refresh BackgroundQuery:=False
End With
Next i

ActiveSheet.PivotTables("Consolidated Pivot").Refresh Table

End Sub

I tried a simple debugging code to count the number of query tables on each Worksheet and got back a value of "0".

Any help you could provide would be appreciated. Thanks!

 

[SkipVought]

Hi,

my organization's critical services, broken down into 14 Worksheets for each division.

I assume, that YOU created these 14 worksheets. If so, you have shot yourself in the foot. ALL your similar data (my organization's critical services) oiught to be in ONE TABLE on ONE SHEET. Doing so would preclude all the handstands and contortions involved in your VBA, and would in all lieklyhood be able to be done without a lick of code.

The short of it -- spend your effort consolidating your data.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[abjectpoet]

Hi Skip,

Thanks for your reply. Believe me, if this workbook were my creation, I would simply rollup the data on one sheet as you suggest and be done with it. Unfortunately, I didn't design the Spreadsheet and can't alter its visible form.

Are you saying that this approach to solving the problem is undoable or simply impractical?

Since I am up against a deadline, I have been pursuing an alternative solution while waiting to see what the tech wizards here suggest. Essentially, I'm working on code to copy all data to a single sheet, hide it from view, and produce my pivot from there. This may end up being less work in the end than going through MS Query.

Of course, all of this wouldn't be necessary if the multiple consolidation range tool in Excel were more flexible...

 

[SkipVought]

Just trying to save you heartache, blood, sweat and tears.

"here is the SQL code I used to generate the pivot table:"

Are you saying that in the PT wozard, this was your extrenal data source?

You could try using the same SQL in a sheet, inserting a QueryTable and returning the joined data to that sheet.

Then just refersh the QT and the PC...

Sub RefreshData()
   YourQueryTableSheetObject.QueryTables(1).Refresh False
   YourPivotTableSheetObject.PivotTables(1).PivotCache.Refresh
End Sub

That should do it.








Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[abjectpoet]

Hi Skip,

To answer your question:

"Are you saying that in the PT wozard, this was your extrenal data source?"

I fed 14 named ranges (1 for each Worksheet) into MS Query, used the SQL to mash them into a single data source, and exported that data back to Excel as a Pivot Table. I entered the PT Wizard directly from the Querying Tool and was not asked if I wanted an external data source.

I've kind of wondered if this might be part of the problem. Should I have instead saved the Query and accessed the PT Wizard from Excel-side?

I have to confess that I'm not sure what you mean by "using the same SQL in a sheet." I've entered SQL in the box for that purpose in MS Query but are you saying I can somehow enter the same code on Excel-side? If yes, then where would I be "inserting" a Query Table?

Sorry for the ignorance. Your four-line solution looks great but I'm not getting what I need to do first procedurally.

 

[SkipVought]

What do you mean by, "on the Excel side?" What "side" are you on?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[abjectpoet]

By "Excel-Side", I meant in the main Excel view (i.e. looking at your spreadsheet) rather than, for instance, in the VB Compiler or the MS Query tool. Sorry, just a bit of work lingo I threw in without thinking.

So, to carry the explanation a bit further, I was wondering if I should have saved my query in the MS Query tool and then returned to the main Excel view to generate the pivot table from there using the saved query as the external data source? Would this be different somehow then generating the pivot table directly from MS Query?

 

[SkipVought]

My original suggestion regarding comsolidating your data, can be easily accomplished using your join SQL.

faq68-5829

Put your QueryTable in a [blue]new sheet[/blue].

Then point the PT source data to the table in that [blue]new sheet[/blue].

Hence my former code suggestion...

Sub RefreshData()
   [b][blue]YourQueryTableSheetObject[/blue][/b].QueryTables(1).Refresh False
   YourPivotTableSheetObject.PivotTables(1).PivotCache.Refresh
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[abjectpoet]

Hi Skip,

I appreciate what you're saying but I would prefer not to double the size of my workbook. By this I mean that I can't remove the original 14 worksheets with the split-up data. So, if I create a consolidated data sheet (whether by using MS Query or copy and paste), I'm going to end up with the same data twice.

The ultimate goal was to pass the data from the separate tabs to MS Query and to generate a pivot table directly (which it can do instead of generating a Query Table on an Excel Sheet). But, if I do that, it won't refresh. After using some VB Code to count Query Tables, it came back as "0".

I can and already have developed a stop-gap approach to my problem. Using VB, I have copied and pasted all of my separate tables onto a single sheet, hidden that sheet from the client's view, and generated the pivot. Clearly, MS Query isn't required to create a consolidated data sheet. But the resultant Workbook is double the size.

I'd just like to know if generating and refreshing a PT from MS Query without the consolidated data sheet has a chance at working or whether it would be too much trouble?

 

[SkipVought]

I'd do the query in a separate workbook, so as to not blote the workbook size. But it seems you may already have a solution.

Seems strange: I can query sheets in my workbook but NOT IN THE PIVOT TABLE WIZARD. I never actually tried before.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[abjectpoet]

Hi Skip,

Thank you for all your effort. As you indicated, I have a solution -- not an ideal one, but better than none.

It seems like pivot and query tables are wholly different animals, regardless of whether you built your pivot from data assembled by MS Query. I hope that Microsoft plans to support multiple consolidation ranges better in future versions of Excel, as the current workarounds are a pain.

Unless you or someone else has something to add, I'm going to call off the hounds and settle while I'm ahead.

 

[SkipVought]

Good luck! [Howling@TheMoon] ;-)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}