We help IT Professionals succeed at work.

consolidate 50 sheets into one workbook easily, but without vba macro

rberke asked
my workbook has 50 worksheets, one for each state.

Each sheet has identical structure in cells a1:b6
Region     Sales
NorthWest $100
NorthEast $200
SouthWest $300
SouthEast $400

I need to consolidate them into a pivot table.
One way to do this is to use the consolidation wizard as follows

1) alt d p
2) ins step 1 I select: Multiple consoslidation ranges
3) in step 2a I select: I will create the page fields
4) in step 2b: I add Idaho!A2:B6
               I specify I want 1 page field
               I specify that that page field's name is "Idaho"
5 I then repeat step2b 49 more times.

Naturally, this is very cumbersome. I might use a macro recorder to automate the 50 steps, but I wonder if anyone knows a better way?

I tried using a 3d reference like 'Idaho:Wyoming!a2:b6' but that did not work
Watch Question

Rob HensonFinance Analyst

Once you have done it once, the Report should be updated with new data each time it is refreshed.

To make it more dynamic, you could set up the ranges on each sheet with a dynamic named range to which you then refer in the Consolidated Pivot Table wizard.

For each named range you can use the following formula in the "Refers to" section of the Name Manager:


This will create a range on the Idaho sheet, starting at A1 and including as many rows as there are entries in column A and two columns wide.

Change the sheet names in each part for each sheet, but again you will have to repeat 49 times.

Rob H


=OFFSET(Idaho!$A$1,0,0,COUNTA(Idaho!$A:$A),2) was a clever idea that should have worked, but does not. It says cannot open pivot table source file.  Luckily, every worksheet is exactly 6 rows high, so I don't need to use your trick.

Also this is a one shot report, so new data will not be an issue.

So, I am still looking for someone to suggest a way to avoid 50 repititions of step 2b

Rob HensonFinance Analyst

If all of your sheets are a fixed number of rows you can probably consolidate the data onto a Summary sheet fairly easily with a couple of formulas.

Do you have or can you get a list of the sheet names? As they are state names no doubt a list would be available somewhere.

If so, copy the list into a new sheet and then copy and paste 5 times so that the list is repeated 6 times. Then sort the list so that each repetition of the states are now grouped. in the next column put the following formula and copy down the full list:


As this goes down the column the count should increase from 1 to 6 for each state.

In the next 2 columns the following formulas, copied down:


Hope this helps
Rob HensonFinance Analyst

Looking at the sample in the question, am I right to think that the sheet name is repeated in a1 and then the data is a2 to b6 with headers in a2 and b2 and then data a3 to b6?

If so the offset formula didn't work because it was creating a range a1:b6. B1 of this would be empty and the pivot table would be looking at a1:b1 as headers and needs headers for all columns.

Therefore change to:
=OFFSET(Idaho!$A$2,0,0,COUNTA(Idaho!$A:$A)-1,2)   The -1 after the count is required to stop the range from having a blank row which would have to be filtered out of the pivot.

If going with the INDIRECT option on a summary sheet, you can get rid of the repeated headers by only having four rows for each state and change the countif with +2 on the end, the counts will then go 3 to 6, the data rows.

Rob HensonFinance Analyst
Got the bit between my teeth, as they say.

See attached file.

Copy all but the the Idaho sheets from this file into your file with all the states and refresh the pivot. Assuming the State names are correct, it should hopefully be what you want.

Rob H


Your use of indirect and address functions are mighty slick, and solves this particular problem.

But, for future problems it might be less satisfactory. For instance, I have another project that I did last week where there were 150 worksheets that each had 50 rows to be summarized.

With your method, the summary sheet would have 150*50 = 5250 rows.  Plus,  when sheets contain a variable number of rows, it needs manual adjustments.  I put the following "alert formula" into cell  E2 of the summary table and copied it down.  It puts and "x" next to states that need attention.   The countif counts the total number of "Ohio" rows in Summary sheet,  The  Counta counts the total number of data rows in the "Ohio" sheet. Please note, $a100 is NOT a typo.


In fact the clever and skillful method gets downright ugly, so I am not going to use it after this one project.

Finally, I played with you Offset(...) idea, and it simply does not work, no matter what I do. when I click add for one of those Offset ranges, the wizard opens the correct sheet and puts chase lights around the correct cells.  But, when I finish the pivot table, it generates an error.  I even deleted all of the header rows, and it still does not work.  Seems like a bug in Excel 2010.   You can try it on the attached sheet.
Rob HensonFinance Analyst

How were you using the OFFSET function?

The copy of the formula that is on the Idaho sheet is wrong. The syntax of OFFSET is:


StartPoint = a single cell, you had it as a range
RowsOffset = number of rows to move away from the start point before starting the required range
ColumnsOffset = as per RowsOffset but columns
Height = number of rows to include in required range
Width = number of columns in required range

So you use the OFFSET function to create a named range, like you have with your range1 and range2 but use the formula instead of Sheet and range reference.

Pivot Table data has to have headers and they have to be included in data range so need to add the headers back.

I have just done the above and have created a Consolidated pivot from Range1 and Range2 without error.

Pivot shows 4 rows for regions and total of sales for each.
Rob HensonFinance Analyst

Looking back at original question, why are you averse to using VBA?

Sounds like you do this sort of summary reasonably often and a consolidated pivot sounds like your preferred option.

A VBA routine could be written (probably not by me though) to run through a workbook and look at each sheet, copying the data to a summary or creating a formula driven summary or creating a named range and adding it to a consolidated pivot data set. I am surprised no-one else has chipped in with a VBA option.


"Why am I adverse to using vba"  ???

Pivot tables are incredibly powerful, with many many options.  I know a bunch of the options, but I was hoping that some pivot table guru would know a way to use a 3d reference.  For that matter, perhaps that feature exists in Office 2013, which I do not have.

Also, I am quite expert at vba and my gut feeling was that this particular task might not be as simple as it seems.  And, it turns out that I was right  !!!     I tried to simply replace  
Array(Array("'Idaho'!R1C1:R5C2", "Idaho"), Array("'Iowa'!R1C1:R5C2", "Iowa")), _
with  my50StateArray, _

but that gave error messages.  After playing with it for an hour, I gave up and used a different approach.
It is FUNKY but, I create a vba module on the fly and then run it.  See the attached if you are interested.
I am sure it will need more tweaking but, the approach seems solid.  

I'll leave this open for a day so experts can comment, then I will close it
Rob HensonFinance Analyst

When I was playing with it I was using Excel 2013 and didn't see the options for 3D range.

Looked at your vba script and it bamboozled me; I do some vba myself but this looked mire like vbs and I don't understand it.
Try running it with f8 and you can may discover that it is more straightforward than you think.

The complicated  pivot table commands came from Excel's macro recorder.  In reality, I don't understand that portion myself, I just trust the macro recorder to do its magic properly.  I then took the recorder's code and ran VbaToVba which is a macro I once wrote to turn "real" vba code into a template.  For instance,

the recorded macro's said                                                ActiveSheet.Cells(3, 1).Select        which was turned into
this template line               code = code & vbCrLf & "    ActiveSheet.Cells(3, 1).Select"

My PivotMany used the template and built an array which contains all 50 sheets, then stuck it into the template.  

Like I said, straightforward  (well maybe not  ;-)



Rob's solution worked for this particular problem, but I came up with my own solution that will work a little better on similar problems in the future.
I am giving Rob all the points, but marking my solution as the "best".