-- Edit: this is now part of the bigger question of how to reliably move sheets about --
I've got a workbook which has sheets containing tables and sheet-scoped named ranges. Some of these sheets' formulas link to some of the names on other sheets (i.e. =Sheet1!Sheet1LocalName somewhere on Sheet2).
The time has come for me to create VBA code that moves these sheets into another workbook. Doing so, I of want all existing functionality to remain intact.
When looping over all sheets individually and Worksheet.Move -ing them one at a time to the other workbook, the range name links between the formulas get broken. E.g. when Sheet2 uses a name on Sheet1 in one of it's formulas:
- Move over Sheet2;
- Sheet2 will still correctly link to Sheet1 back in the source workbook.
- Move over Sheet1 itself;
- Excel 'helpfully' creates a workbook-scoped name of the same name for me as the name Sheet2 linked to (even if it didn't exist before), where
- this new name does point back to the already moved sheet in the destination workbook, after which
- the already moved Sheet2's links get modified to point to this new workbook-scope name in the source workbook, thus
- messing up the link beyond repair.
Even if I could overcome this by analyzing the formulas beforehand to scan all sheet dependencies, since sheets may have links going both ways between them, it seems I can't do it this way.
When moving all sheets in one go using ThisWorkbook.Worksheets(Array(name1, name2)).Move, I get the Excel error "You cannot copy or move a group of sheets that contain a table".
So it seems I'm sorely out of luck here... There's of course the options of:
- moving the sheets individually and rebuilding all formulas afterwards, and
- replacing all tables with ranges and rebuilding all tables afterwards
but I'd understandably do not want to go there, since I do not control what goes on the sheets...
Any alternatives?
-- Edit --
The true purpose of this question is that I need to move the sheets over to a temp .xlsx workbook, save/close/re-open that workbook, and then move them back again to the original .xlsm workbook, thus scrubbing them off of their VBA module. See my other SO post for the background, though when I wrote that question I envisioned only needing to scrub the 1 topmost sheet in the ranged-name linking hierarchy, but it turns out I need to do it for all sheets to be safe enough.
After a lot of trial-and-error I found out that moving or copying all sheets in one go just isn't doable because of the tables on them, and handling them one at a time really messes up formulas and named ranges linking them together (even .Copy has similar unwanted side effects to using .Move). While I could in turn write code to 'fix' these broken names or delete these 'helpfully added' rogue names, I wouldn't be surprised if other range linking mechanisms (like chart source, pivot source, data validation list source, form control linked cells, etc.) also acted up badly, making this an even bigger mess to deal with...
Copyrather thanMove. Maybe Excel is less "helpful" with a copy between workbooks. – John ColemanWorksheet.Copyindeed doesn't make Excel invent new range names. Since they're just copies, all formulas getting data from other sheets now point back to the source workbook. So coupled with some code that scans all formulas on the copied sheet to remove the ref to the source workbook, this effectively 'moves' the sheets to the dest workbook as if it was a self-containing set. I'll have to experiment a bit further to see if it's all effective enough; there's more code building up on this in turn. – Carl ColijnWorksheet.Copyindeed also copies all range names, except the ones that are not in use on the sheet itself. So you'd have to add a (hidden) cell to the sheet that references these names to keep them alive in the copy. – Carl Colijn