Copying Excel Tabs Between Files with Power Pivot

[satanbringsusluv]

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.

 

[sandy666]

maybe use Power Query

• What is PowerQuery (Get&Transform)
• Getting started with PQ
• PowerQuery Help

 

[satanbringsusluv]

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]

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

edit:
PQ working with Excel Tables and named ranges

 

[satanbringsusluv]

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]

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]

I just tested from source to the new workbook and it works well with many tables