Copying Excel Tabs Between Files with Power Pivot

satanbringsusluv

New Member
Joined
Oct 18, 2010
Messages
6
Hoping someone has a solution to this, as it would save me tens of hours of time.

We recently upgraded to Power Pivot 2016 from Power Pivot 2010. One of the great features of Power Pivot 2010 (to me) is that if I had two copies of an Excel file with the same Power Pivot model, I could copy an Excel tab (with pivot tables) from one of the files to the other, and Excel would helpfully display a messages along the lines of "We noticed the file you're copying into already has a Power Pivot model with the same name... would you like to connect to that?"

I would click yes, it would connect all pivot tables in the copied tab to the new workbook's Power Pivot model, and life was good.

When I try to do the same thing in Power Pivot 2016, I get no message, and instead Excel copies the entire Power Pivot model from file 1 to file 2, giving me duplicates of every tab in Power Pivot. This double's the size of my file and is a nightmare for the number of times we need to copy tabs between files. It seems our only recourse may be to rebuild pivot tables each time we need to copy them between files.

Please tell me this isn't the case, and that Microsoft didn't just drop this very useful feature. Is there any way to copy a tab from one Excel file to another and have it connect to the new file's Power Pivot model, without creating an entire duplication of all Power Pivot tabs? Please say yes! Thank you in advance.
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

satanbringsusluv

New Member
Joined
Oct 18, 2010
Messages
6
Power Query may be a more complicated solution (I would have to look into it to figure out how solve using it though).

I'm hoping there's a simple way to just copy a tab from one Excel file to another, the way Power Pivot used to do. This is my preferred method because each quarter end we have to copy 10 to 20 tabs between workbooks and we need them to connect to the new workbooks' Power Pivot model.
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,106
simple way to just copy a tab from one Excel file to another
that is what Power Query do and does not increase the file size
more complicated is Power Pivot :biggrin:

edit:
PQ working with Excel Tables and named ranges
 
Last edited:

satanbringsusluv

New Member
Joined
Oct 18, 2010
Messages
6

ADVERTISEMENT

Thanks sandy666. I'll definitely look into that.

In the more immediate term (as redesigning these files to use Power Query would take some time (as they're hundreds of MB large with 30+ tabs of linked tables, etc in Power Pivot), I'm still hoping there's a way to copy tabs from one file to another the way Power Pivot used to do in 2010?
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,106
I never use Power Pivot to copy data from one file to another because Power Pivot use Data Model so it will increase file size that is why I prefer Power Query where I can decide to use or not Data Model
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,106
I just tested from source to the new workbook and it works well with many tables
 

Watch MrExcel Video

Forum statistics

Threads
1,114,280
Messages
5,546,948
Members
410,764
Latest member
Dedeke
Top