• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pivot table with 2 sheets



i am looking for a solution , where i have 2 sheets of data and i want to create single pivot table...2 sheets data is same except month....first sheet is till jan to oct and 2nd sheet is for nov
i know there is option to consolidate sheets in pivot table and i am able to create it but i need mutiple option to be in filter category in pivot, which i am not able to do...

untill now i have created pivot table (sheet attached) but i want Area,category and region in filters and common name in rows , count of ACT1 in values and month in columns.

Any suggestion appreciated.



New Member
Hi, pivot tables do not support multiple tabs. You can use a QUERY formula instead though, and construct your dataset as a vertical array, for example
=QUERY({StaffSheet1!A:Z;StaffSheet2!A:Z},"select Col1,Col2")
You can add all 18 tabs between the { } and separate each range with ;
If you need help with this, please Share an example sheet wash containing dummy data and paste a link here along with a description of how you want it to work


Hi Chihiro....i dont have powe query installed in my machine...what can i see is powerpivot,powermap and powerview.....

I tired to run a macro for the same link, which you have provided above but it is only creating a another sheet without making any pivot from 2 sheets....

Topaazy....is the forumula you have used starting from Query is again can be used in powerquery......please explain..thanks


And why i need is because my first sheet rows are filled up till 1048576 and my data is till jan to july and for Aug month data i need another sheet and now i want want to create pivot from these 2 sheets...


Excel Ninja
Hmm? If you have PowerPivot, you should have PowerQuery available. Check if you have "Get & Transform Data" in Data tab. If you do, you've got it.

If not, you'll need to adopt the code, to suite your need.

It will look like it added new sheet without anything. But if you select B4 of newly added sheet. PivotTable Fields will appear.

It is left that way, to leave design of the pivot up to the user.


Code given in above link is working file to file attached there...but when i try to run on my sample sheet it gives error "User defined type not defined"...can you plz look into this.....thanks




After tried and testing several times, it is now giving something else error “Multiple step operation generated errors. Check each status value.”

This error is in Record set code and on “rs.Fields(col - 1) = data(index, col)”

What would be the reason for it?