Free Microsoft Excel 2013 Quick Reference

How to copy pivot table workbook to new workbook?

I have been given an invoice workbook with data and pivot table. I am to
copy this workbook on a monthly basis and update the month and any related
invoices so that there is a separate workbook for each month. When I copy
the entire workbook I find the month column on the pivot table doesn't
include the new month's name. Also, there seems to be a link from one book
to the next, how do I copy this book, add the new month's name and keep the
link to the preceding book all at the same time?

Post your answer or comment

comments powered by Disqus

Anyone know how to update pivot table when new rows/columns are added to its original data without redoing the whole thing again?


How to clear pivot table catche. because the fileds dropdown list contains
earlier values. so is there any solution for this. If possible without VBA

Advanced Thanks,

I need to explain how to use pivot tables/charts to a group of people tomorrow. The tables are already created, just need to show them how to add fields, remove fields, general overview. Can anyone suggest some resources with concise explanations (hopefully have pictures too)?


How to sort Pivot table columns? Please Help. Thanks in advance


How to copy filtered records into new worksheet. if i copy the filtered record, hide records are also copied. Please Help!. Thanks in advance


Using Excel 2003 VBA and I am so new to this. I am trying to copy pivot tables located on separate worksheets (sheets 2 thru 9) from a workbook named Ancillary_Final Output.xls. Each sheet in this file has one pivot table on each sheet. The sheet names are below:

Sheet 2: IP Summary
Sheet 3: IP Facility
Sheet 4: IP Facility by BOB
Sheet 5: IP- Facility Type
Sheet 6: OP Summary
Sheet 7: OP Facility- Top 5
Sheet 8: OP Facility Serv Type- Top 5
Sheet 9: OP Service Type Detail
Sheet 10: OP by BOB

I simply want to copy and paste the values of each pivot table into another workbook named Ancillary_flat.xls with the same sheet name. Each month all pivot tables will be refreshed and copied to this worksheet which will have formulas tied to the pasted values which will drive reports in separate files. I can’t seem to get this to work, and it is holding up my other projects. I am willing to pay $15 for a solution.

The code I have will accomplish it, however it creates duplicate worksheets with a (2) at the end of the sheet name. I get tired of having to delete sheets and type in formulas. Here is the code I have:

     ' copy before (or after) some particular sheet
    Workbooks("Ancillary_Final Output.xls").Sheets(2).Copy _ 
    Workbooks("Ancillary_Final Output.xls").Sheets(3).Copy _ 
    Workbooks("Ancillary_Final Output.xls").Sheets(4).Copy _ 
    Workbooks("Ancillary_Final Output.xls").Sheets(5).Copy _ 
    Workbooks("Ancillary_Final Output.xls").Sheets(6).Copy _ 
    Workbooks("Ancillary_Final Output.xls").Sheets(7).Copy _ 
    Workbooks("Ancillary_Final Output.xls").Sheets(8).Copy _ 
    Workbooks("Ancillary_Final Output.xls").Sheets(9).Copy _ 
    Workbooks("Ancillary_Final Output.xls").Sheets(10).Copy _ 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I have two sets of pivot tables that are currently in two different Excel
books. I copied the sheets including pivot table and source data sheets from
Book A to Book B, but when I tried to refresh the data for the tables now in
Book B, an error message appeared stating that the data [from Book A] could
not be found. Any suggestions of how to copy pivot tables from one book to
another would be quite helpful. Thanks.

Hi all
i am trying to use macro to split pivot table , but each time the number of rows in the pivot table changes according to the number of "service providers" in my main sheet
can anyone please help me with a vba code which can "double click or show details" on each of the row in pivot table and copy that in to another "WORK BOOK" t and name it
am stuck with this for long time , it would be really helpful for me
the pivot table always is in b and c column
in b column = name of "service provider"
c= values or counts
thanks in advance


I'm new in excel programming and I'm seeking to all gurus out there to help me about my excel problem.

I have two or three different table in one single sheet (Sheet1) which is separated to each other from a single row with there respective fields. I want to copy each table without the header and transfer there rows to each respective excel File (XFile1, XFile2, XFile3).

Hope your support about this.



I've created a Excel Add-In using Excel 2007 and created a OLEDB connection with this Add-In to create pivot table with that conenction. I've added that OLEDB connection to Add-In (not for activeworkbook) for security resons (If I add the connection to the activeworkbook with help of Add-In my username& password will be displayed in active workbook connection properties).

I'm trying to create pivotCache to create pivot table with Add-In's OLEDB connection, it is raising error(1004: Application-defined or object-defined error).

The following code used to created pivot table cache
ThisWorkbook.Connections.Add "OlapConnection", "", Array("OLEDB;Provider=MSOLAP.3; Persist Security Info=True;Data Source=datasource;Initial Catalog=dbname;roles=Role2005;TimeOut=5000;User ID=UserID;Password=password;"), Array("SampleCube"), 1
Set pc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:=ThisWorkbook.Connections(OlapConnection), Version:=xlPivotTableVersion12)

Can anyone help me to resolve this issue?

Thanks in advance!

Is it possible to amend pivot table structure, exchane row, column and data

I have a workbook with multiple worksheets. Values are gathered via links,
which may not be accesible all the time. Therefore, I am thinking of easy way
to copy the whole workbook without links, just like with the function Paste


I'm copying a pivot table and linked pivot chart from the same worksheet to a new workbook. The pivot table works fine in the new workbook but the pivot chart seems to have lost its linkage to the pivot table data.

Can anyone help with this please?



How would I copy Pivot tables from one workbook to another? I have a
workbook of time sheets that I have to do a "study"; i.e. based on the
desciption of time spent per day per employee. I have many sheets that is
taking me forever to do them individually. I tried recording a macro for
each Pivot Table I created for each day of the week, but that did not work;
orit could be that I am not applying the maros correctly. Can anyone help me?

When I copy a worksheet pivot table and its source worksheet from workbook 1
to workbook 2, the copies in workbook 2 appears to come across fine, but
when I update the source worksheet in workbook 2, and refresh the pivot table
in workbook 2, no update to the pivot table occurs. Is this just an "Oh
Well", or do any of you have a suggestion for a way to make this work? thx
so much!


I have to excel files, both have the same data. I created a pivot table macro on the first file. I want to be able to open the second file and use the shortcut to create the pivot table. the problem is that it refers back to the original workbook I created the macro on. How do I change the code to make it refer to the current workbook? Thanks

Sub Pivot()
' Pivot Macro
' Keyboard Shortcut: Ctrl+p
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"mckenziecostadmpp4!R1C1:R1680C13", Version:=xlPivotTableVersion10). _
CreatePivotTable TableDestination:="Sheet1!R3C1", TableName:="PivotTable1" _
, DefaultVersion:=xlPivotTableVersion10
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Cost Element")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Amount"), "Sum of Amount", xlSum
Sheets("Sheet1").Move After:=Sheets(2)
Sheets("Sheet1").Name = "Pivot Table"
End Sub

I'm building an Excel 2007 workbook that contains multiple pivot tables which are all based on same pivot cache. The data source is external and retrieved by a SQL query from an Access 2007 DB. The SQL query may be changed for a purpose so a new pivot cache is created when it's modified.

I can use Pivot Table Wizard to manually steer all pivot tables to new master copy of pivot table without a problem. But I want to use VBA code to do the process to same time as I have more then 10 pvt in the workbook. So I use the Excel's macro to record a manual change. Below is what is the VBA code produced by Macro:
    ActiveSheet.PivotTableWizard SourceType:=xlPivotTable, SourceData:= _
    ActiveWorkbook.ShowPivotTableFieldList = True
pvtMstrCopy is the name I give to the master pivot table. Cell E77 is inside the target pivot table which I want to change data source.

But the problem is I can't even re-run this recorded macro. Excel report error message Run-time error '1004' PivotTableWizard method of Worksheet class failed I add a sheet name to SourceData parameter to "Sheet1!pvtMstrCopy" and it still has the same error.

It's frustrating since I've been searching same error in various Excel forums for 2 days and I didn't find same one.

Can anyone tell me how to use VBA to change pivot table data source to another one? Thanks in advance.

I have some data in a pivot table which I want to use in a calculation but
when I try to key off a cell in the table the new cell is a calculation
....GetPivotTable....... and it is impossible to drag this cell down to
other cells that correspond to other cells in the pivot table.
Am I explaining myself??
Or: How do I refer to the contents of a cell in a pivot table?


I have a spreadsheet that has multiple pivot tables on different tabs. This program is going to people that do not know how to manipulate pivot tables, therefore I want to protect each sheet, only allowing access to the page fields.

I have:
INPUT SHEET containing PivotTable1

BAKESHEET containing a PivotTable30 that is based on PivotTable1

ANALYSIS containing three pivot tables each bases on PivotTable1

I have a routine that copies the newly inputted information over to the database (seperate file). the user then needs to return and be able to access that information on the BAKESHEET pivot table, which will need refreshing before displaying the new page field.

I have tried to use the workbook.activate functino to refresh pivot table 1, but I get the message cannot refresh pivot tables based on this one if they reside on a protect sheet (or words to that effect) I can't base each of the pivot tables on the original data as the file size is huge.

any advice would be greatly appreciated



Hi there!

I've created a document with many pivot tables. After creation of the entire document, a new addition was requested, which called for a calculated field.

I went to the first Pivot Table, created the calculated field and inserted it. Worked great.

But now I need the same field in every pivot table in the workbook.

This calculated field shows up in the field list, but will not allow me to drag it into new pivot tables. Error message is "The field you are moving can not be placed in that Pivot Table Area". But it is a calculation/field created for that area. (And worked perfectly in the first pivot table)

I've tried going to the pivot table toolbar, finding the custom calculation and hitting ok. The pivot table refreshes, but does not add the calculation.

I've even tried recreating the custom calculation in each successive pivot table, but of course it will not let you name them the same. So even this time-waster will not work.

Am I approaching this wrong? Any suggestions?


I'm attaching the file that I need help with. When you open the file, it displays a dialog asking if you want to "View Data" or "Begin New Audit." If you click "View Data," it closes the dialog box. If you click, "Begin New Audit," it creates a new sheet based on a Master Sheet, and it names the new sheet based on the current date (in yyyymmdd format). The workbook contains a pivot table that needs to update with information from the new sheet. I can update the pivot table manually (I'm using multiple consolidation ranges), but I need to create a button that updates the table with the ranges from the new sheet when clicked.

I know the answer probably lies in using named ranges, but I don't know how to use named ranges with new worksheets. Please help!

I need to link a sheet to a pivot table in the same workbook to get the data
value from the pivot table. This also has to be updated dynamically as the
pivot table changes. Can this be done and how.


I have a pivot table A created in Excel 2003 with the data source linked to another pivot table B. How do I change the data source of pivot table A link to a new pivot table C in Excel 2007? Can Excel 2007 create/change pivot table with data source link to another pivot table? I can't seem to find any function for that under Excel 2007 Pivot table commands. How do I resolve this? Please help.


Hi all,

I have a pivot table which reads from a fairly large list of entries (about 17000 records). The table was tedious to build, so I typically update the data, then refesh the table to produce the new results. The issue I have is that the pivot table field filter dropdown boxes tend to accumulate obsolete data over time and I cannot figure out how to reset these so that only the current values are shown.

For instance, I have a field "Prog" which lists various programs in progress. When I created the table, Prog1, Prog2, and Prog3 existed, and the pivot filter dropdown listed all three. Over time, some of these programs completed, and the records were deleted so data no longer include Prog1 and Prog2. New programs Prog4 and Prog5 were also added. However, the field filter list box shows all 5. (While this is not a big deal with 5 projects, it is wihen the list climbs to 50.)

If anyone can tell me how to reset this without completely rebuilding the pivot table, I'd appreciate it. I'm using Excel 2003 SP3 and Windows XP.


No luck finding an answer? You could always try Google.