1

-- 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:

  1. Move over Sheet2;
    • Sheet2 will still correctly link to Sheet1 back in the source workbook.
  2. 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:

  1. moving the sheets individually and rebuilding all formulas afterwards, and
  2. 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...

Community
  • 1
  • 1
Carl Colijn
  • 1,423
  • 9
  • 29
  • Sounds like a tricky problem. Could you convert the tables to ranges before moving the sheets as a group and then afterwards convert them back into tables? – John Coleman Jan 24 '17 at 12:39
  • @JohnColeman: rather not - I do not get to control the content of the sheets, so writing code that reliably reconstructs the tables afterwards (e.g. style, column formulas, etc.) would be quite an undertaking... – Carl Colijn Jan 24 '17 at 12:48
  • Perhaps you could `Copy` rather than `Move`. Maybe Excel is less "helpful" with a copy between workbooks. – John Coleman Jan 24 '17 at 12:53
  • Thanks for the idea! I tried this, and `Worksheet.Copy` indeed 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 Colijn Jan 24 '17 at 13:20
  • One more tip for anyone following me down this path: `Worksheet.Copy` indeed 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 Jan 24 '17 at 13:22
  • @JohnColeman: bad luck; while this seemed promising at first, some of the sheets also contain workbook-scoped names that others use (e.g. in data validation lists), and when copying the sheets individually, these get 'promoted' to dummy sheet-specific names in the copies themselves. Which also made me realize that although I've added code to correct the formula references on the copied sheets, I'd also need to do that for all other objects accepting a range name as their source... what a mess. – Carl Colijn Jan 24 '17 at 13:36
  • I have no ideas, but perhaps "The time has come for me to create VBA code that moves these sheets into another workbook" is an XY problem. Perhaps there is a way to leave the original workbook intact but link the two workbooks so that the target workbook can get the information it needs from the other workbook with no need to merge them into a single workbook. – John Coleman Jan 24 '17 at 14:14
  • @JohnColeman: sorry, but I actually need to copy or move the sheets to a new workbook, and then back again; see http://stackoverflow.com/questions/41745559/copying-sheets-while-preserving-digital-signatures To summarize: in order for my digital signature to stay on the workbook when my client works with it, I need to be able to create a version of the workbook in which all sheets have no VBA module attached anymore. So I need to 'scrub' the existing sheets via a temporary .xlsx file, without them losing any of their functionality. – Carl Colijn Jan 24 '17 at 14:21
  • You can find a workaround [here](https://blogs.office.com/2009/08/31/copying-worksheets-with-a-list-or-table/), it does copy/move sheets in _one go_, but you need to test if all the dependencies are still OK. – BrakNicku Jan 30 '17 at 16:20
  • @BrakNicku: that method works wonders; using it, I can move all sheets in one go, tables or not! However, the range names get messed up identically as with the sheet-at-a-time method... bummer. – Carl Colijn Jan 30 '17 at 17:08

0 Answers0