I have a number of tables on different Excel worksheets that are connected to various charts and are used in Named Ranges. I need to move them to a different worksheet (housekeeping before things get too far out of hand), but there doesn't seem to be a simple way to do so. I'm looking for something similar to the Move Pivot Table option, only for basic tables.
So, how do I move Worksheet1!Table1 to Worksheet2!Table1 and maintain all of my links?
EDIT: What I've tried so far:
The obvious Copy & Paste, but that requires deleting data sources, renaming Ranges and generally mucking around with a lot of minor (but important) tweaking that seems like it should be managed by Excel. Oh yeah, and Google (to no avail).
3 Answers
The slightly less obvious answer won out-Cut & Paste.
Copy & Paste created duplicate everything and didn't fix the problem. However, Cut & Paste carried all of the associated links to the new location. Got a brief error on a page with linked charts and formulas, but it caught up in a few seconds and showed the correct data.
EDIT: There appear to be some limitations to the size table that can be cut and pasted. I have two tables that were to be cut and pasted from and to the same worksheets. The 300 row table worked fine, the 5000 row table did not-it crashed Excel (5 different times as I was attempting to fix it). It's not the specific table, because when I changed it's query properties to return fewer rows (about 300 rows), it moved fine.
There is a new Excel add-on called Power Query that allows you to copy tables.
From the target file, use the Power Query to load from the source file. The add-on will allow you to select which table(s) you wish to select.
Once moved, the Refresh button will read from the original source file, allowing the source file updates to refresh the target file on demand.
you can move sheet whereever you want by Right clicking on sheet name in workbook, click on Move or Copy and don't forget to select create a copy check box
1
