How To Combine Multiple Pivot Tables Into One Table, Vlookups?

Free Excel Help Forum

- Ask any question about Excel and have it answered in no time.

Similar Excel Tutorials

Guide to Combine and Consolidate Data in Excel
Guide to combining and consolidating data in Excel. This includes consolidating data from multiple cells, multiple ...
Complex Structured References (Table Formulas) in Excel
How to use complex structured references, table formulas, in Excel. If you don't already understand how structured ...
How to use Vlookup Across Multiple Worksheets in Excel
This tutorial shows you how to use the Vlookup function across multiple worksheets within Excel.  This means that y ...
Combine Values from Multiple Cells into One Cell in Excel
There are two easy ways to combine values from multiple cells in Excel. In order to do this, we need to do what is ...

Helpful Excel Macros

Combine Multiple Workbooks into One
- This macro for Microsoft Excel allows you to combine multiple workbooks and worksheets into one new workbook and workshe
Basic Web Query in Excel - Import Data from the Web into Excel
- Import data from the web into Excel with this macro. This is a basic web query macro that will import data from any web
Highlight Every Other Row in a Selection in Excel - Table Formatting
- This free Excel macro will highlight every other row in a selection of cells with a color that is specified within the m
Filter Data Sets on Multiple Columns with Multiple Criteria at Once in Excel - AutoFilter
- This Excel macro allows you to filter a data set on multiple columns and criteria at once. This means that you can filt
Filter Data in Excel to Show Rows/Data That Meet Multiple Criteria for One Field - AutoFilter
- This free Excel macro filters data in Excel based on multiple criteria for one field in the data set. This macro uses t

Similar Topics

I have two separate worksheets and each worksheet has a pivot table. Is there a way to combine the two pivot tables. Basically the area that counts the data need to be combined.

I can do the obvious which is of course copy each for values only and merge them that way but thought there was a way to merge two pivot tables.

Hello All,
I have searched the boards and have not been able to locate exactlly what I need. I am hoping that once again I can learn for the best. I apoligize up front if this topic has already been covered. I have 3 pivot tables in the same worksheet. Each pivot table pulls from the same pool of data and is layed out exactly the same. Each pivot table keys off of different row lables. I am utilizing 2 report filter options. What I am trying to accomplish is for 2 of the pivot tables to update to the same report filter options that I enter in the first pivot table. I have forud code that works with all pivot table in all worksheets with one report option. I just want the 3 pivot tables in this one sheet to work together. Any and all help will be greatly appreciated. THANKS!!

I am trying to write a macro that changes the source data for multiple pivot tables on multiple sheets. here is what I have so far. However it seems to only update 1 Pivot table per worksheet and crashes once it hits a sheet without any pivot table. Any help is appreciated.

Sub AllWorkbookPivots()
Dim pt As PivotTable
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
ws.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
Next pt
Next ws
End Sub

I am making a button that will print multiple pivot tables with a single click. But the tables will update periodically. So I am looking for a way to 'name' the ranges to which the pivot tables belong in the macro, then print the named range, rinse and repeat.

I think I can do most of it by simply recording the macro... but, Can someone help me in finding out how to set a range to reference a pivot table as a whole, rather than manually select the range to which the pivot table belongs each time?

My aim is to insert multiple pivot tables on one sheet (BELOW) each other for project expense reporting.
All works fine except for the positioning of the second pivot table insertion point as the top pivot table has different number of output rows per project.
Would a macro running each pivot table be the answer- then to establish the last row of data, insert a blank row before running the next pivot table
Your suggestions are appreciated. I am not very well trained in macro's and would like some inputs please.
Thank you


I'm stuck with too many rows (250 000 and counting) of data that I need to manipulate in a Pivot table. If I could use my Excel 2007 this would be no problem at all but I have to make it work in Excel 2003.

I tried to handle the problem with consolidated Pivot Table but I wasn't too happy with the results. Too few options and a lot of work to get rid of the "unnecessary" columns of data. I also tried combining 5 "normal" Pivot Tables with a macro to combine the data into one single "normal" table but if anybody needs to change the row/column fields in any way, the whole combination thing is going to fall apart.

Now I'm thinking if Access could be used as a help but the problem is I have no experience whatsoever about Access and the deadline is on Wednesday. And the whole thing should be done with macros so that it'll be as easy as possible for the users to add more rows data to the pivot tables.

I've understood that Pivot Tables are not limitted to any number of rows they can handle but the only limitation is the computer memory available.

What I'm after is a simple way to combine 5-6 full worksheets of data into a one "normal" Pivot Table. All the worksheets will have all the same columns (30 of them in each worksheet) and up to 55 000 rows of data on each worksheet.

Could anybody help me out by coming up with a way to solve this problem?

I have several pivot tables within one spreadsheet. All pivot tables have
the exact same criteria within the drop downs (eg. country, region, city).
The "data" presented in each pivot table is different (eg. revenue, gross
profit, net income).

Is it possible to link all pivot tables so when a user changes, for example,
the country in one pivot table, it updates all other pivot table data with
the selected country values?

I appreciate any help you may have.



Hi. I have multiple pivot tables in multiple worksheets - on each worksheet I have a source pivot table (OLAP cube) and 4 pivots that work off this data.

In my macro I go through each worksheet, change the necessary olap cube fields and then i want to refresh all the pivot tables in that sheet - i do not however want to refresh all the workbook.

I can only seem to do this by refreshing one specific pivot table eg

OR refresh all in the workbook eg

Is there a way to do each sheet individually?!


Hi all - hope you can help me as my boss is killing me over this!

I am having a lot of trouble with pivot tables in Excel 2007 VBA. I am trying to create pivot tables using macros (connected to buttons the user can press to create the pivot table) - please don't ask why, but i need to do this!!!

I used the record fuction in excel 2007 to produce macro code which will produce the required pivot tables when the user presses a button.

Unfortuanately the coding seems to work fine when i have one pivot table in a file but breaks down if i record code to produce another pivot table.

I have attached some code below (which was produced by the record function) and is intended to produce 2 seperate pivot tables (the macro submacro2 produces the 1st pivot table and the macro submacro4 lower down the page produces the 2nd pivot table). I have also indicated the point in sub 4 where the code breaks down - basically submacro4 just doesnt run!

I hope you guys can help me and suggest corrections to the code in sub4 so that I can produce the required 2nd pivot table (and perhaps more besides!)




I am using Pivot Tables within Excel 2007. I will be distributing this spreadsheet to multiple users to be able review their data within the Pivot Table. I would like them to provide comments in the first available column after the Pivot Table - and based on these comments I will take appropriate action (e.g. disable user accounts, delete certain products, etc). However I have found that when comments are included in the row next to the Pivot Table data, they will stay static and not move with the pivot table data should the user change the Pivot Table variables. I could tell users to review the data using the Pivot Table and then make the appropriate comments in another worksheet, however this will take extra effort. Is there a way to add comments next to Pivot Table data that will stay with that row of data regardless of when the variables within the table are changed?

Many thanks.

I have 2 pivot tables set up on one sheet in a work book the pivot tables are linked to a report that has to have rolling totals and averages. My solution was to create two pivot tables one to calculate the total and one to calculate the average.

I would like to auto refresh both pivot tables by clicking on the worksheet tab. I am able to refresh the top pivot table with this code, is there a way to refresh both pivot table by selecting the tab?

Private Sub Worksheet_Activate()
End Sub

Hi there-

I have a pivot table on the left hand side of my worksheet

I have columns to the right of that pivot table that I use to add comments to data in the pivot table.

How can I apply an autofilter to the pivot table so that when I change the row criteria in the pivot table the data in the columns to the right of the pivot table sort/hide/show along with the pivot table?

I've seen settings where an autofilter is applied to the pivot table, but I cannot figure out how to do that...

Hi. I have several workbooks containing multiple worksheets with atleast one pivot table in each sheet. In each pivot table, I need to change the AutoSort setting to "Ascending" of a specific field, say "month".

Can someone help me write a VBA code that loops through all sheets and pivot in the active worksheet and sorts "month" in ascending order without having to call the assigned pivot table name and sheet name.

Thanks a lot! Newbie here.

Have been trying to combine multiple Excel files in one folder to one table in Access. All excel files are the same format. Working with versions 2007 in excel and access. Will be appending the excel files to one table and each month an excel file gets added to the folder so need the capability to either combine all tables or choose files to combine would probably be better. Other option is to be able to select the excel file that needs to be appended to the table each month. Any help is appreciated on this topic, I will keep searching, any direction on where to search is a big help too, thanks.

Hi there.

I'm creating a budget spreadsheet with multiple pivot tables on each worksheet. I'd like to disable the ability to select certain fields to prevent novice users from using them by accident and getting confused.

The thing is that I already have code. And it works on almost everything. Then it just stops working. For example, on the Apr and May sheet it worked on every pivot table on the worksheet. Then on June it only worked on 2 of them. Then on July, it worked on the same 2. But it did work on all the pivot tables on each page previously. So I assume this is some kind of error or glitch. Can anyone provide any help? Or can anyone tell me how to design it to run on a selcted pivot table instead of Active Sheet?

Thanks so much!!!

The code I'm using is:


Sub DisableMonthSelection()
Dim pt As PivotTable

Set pt = ActiveSheet.PivotTables(1)

 pt.PivotFields("Month").EnableItemSelection = False

End Sub

Help! I have a workbook full of pivot tables from which I create reports (with 2 worksheets of corresponding data). For some unknown reason, this month when I updated the data and went to refresh the pivot tables, I get the following message: "Pivot Table cannot overlap Another Pivot Table"

There is no reason for me to get this message - I did not change the layout on the worksheet and there is not more than one pivot table on the sheet. Further, I get this on many of my sheets, not just one. I also tried going back to an earlier version of my file, updating it, and the same thing happened.

Can anyone help me with this? I really do not want to have to recreate my workbook/pivot tables, and I do not want to run across this in the future without a solution.

Thanks much.

I have a Pivot Table based on data that gets imported.

The number of rows in the Pivot Data varies depending on the underying

Below the Pivot Table are some calculations and then a couple more
Pivot Tables.

I'm building a Profit & Loss report and each Pivot table summarises the
relevant report sections (e.g. Sales Pivot Table, Cost of Sales Pivot

The problem is, when the pivot table refreshes, it can overwrite
formulas and tables below it if the table increases in rows.

Any ideas/suggestions please to prevent this.


T Hunt

I have a table from an external data source, and some calculations in it, from this table I created pivot tables.
When I press "refresh all" it only updates the table and not the pivot tables. When I press it another time it the pivot tables are refreshed.
Is there a way to make sure it's done by pressing the refresh all only once, maybe by changing the order in which excel refreshes the tables?

Is there any way to have more than one Row Label in an Excel Pivot Table construted using Multiple Consolidation Ranges?

When creating a Pivot Table from a single data source, all the relevant column names are listed in the Pivot Table Field List, but when the data is from multiple sheets, only Row and Column are listed as available fields in the field list.

I would like to be able to have 2 or 3 Row Labels in my pivot table, but I can see anyway to achieve this.

Does anyone know if it is possible to do?

I'm trying to combine multiple tables into one master table. Eight people report simple data to me (like number of hours worked) and I'd like to find a formula that would compile them into one list that can be sorted alphabetically (and hopefully automatically). I've attached a document showing what is desired. Thanks you much for your help!

I know this is a common issue and i have found a few solutions but none that i have been able to get to work for me. IF anyone has a code that on the change of a filter on the first pivot table the filter on the rest of my pivot tables also see this change. I have not yet found a working code for multiple filter changes for pivot tables either on seperate ws's or on the same ws.

any help would be greatly appreciated,
Gordon R

So, in Office 2007 I am having issues when a create multiple pivot tables with the same data source. I want to be able to control each independently, but instead, when I make certain changes to one table, it is effects the others as well. For example, when I change the grouping of a date field to show just quarters or years, it will make that change across all pivot tables...very frustrating..

Are they linked? How do I stop this from happening?

Thanks in advance,


I construct pivot tables with multiple 'fields' in the row section of the table. I do this to summarise extensive data sets. Once the pivot table is complete I would like the separate 'field' values to be duplicated on every row for that section and not only on the first row for that section. This then allows me to perform another pivot table on this data. Is there an easy way to do this when the pivot table is about 3000 rows long. To auto-fill each field heading would take me days to do manual.

Apologies if this is covered somewhere in the forum but I just spent a good chunk of time looking for a solution here and on the interwebz and I couldn't find what I was looking for.

Basically I want to turn a data entry table into a frequency table with multiple columns. IE

the original data table looks like this

Person1 Person2 Person 3

and i want this output

Person1 Person2 Person3
A 10 0 5
B 0 10 5

before the last hour of searching for solutions or answers, i spent an hour jiggering with pivot tables without any success. I also tried using the histogram function in the data add-in pack but that seems to only want numeric data. Lastly, I looked though a ton of pivot table tutorials and none of them seem to be able to tell me how to do this.

is this something that can be done with pivot tables and i'm just not doing the right things with the fields? The best I've been able to come up with is 3 separate pivot tables that provide a frequency table for each person. There's got to be a way to do this in one step right? The actual data I'm working with has a lot more people and a lot more possible categories besides A and B, and I'll have to be doing this a lot in the future, so it would be nice if I could do it all in one fell swoop.

Again, thanks in advance for your help and if you want to direct me any links you think would be helpful, that would be great. Thanks again.

Hello guys
I am looking for some assistance to make pivot tables with multiple data values against a control item.
A sample excel file is attached herein with. I am quite new to pivot tables was unable to find out a solution self.

Hope somebody could help me out.