Macro to copy sheet in a special way, please

[1eyedjack]

I have two workbooks open in a single instance of Excel 2003, called (say) SourceBook.xls and DestinationBook.xls
SourceBook.xls contains within it a worksheet SourceSheet.
SourceSheet contains several formulae including single-cell array formulae (but no multi-cell array formulae).
SourceBook.xls contains a number of defined names that are local to SourceSheet.
Neither workbook contains any links to external workbooks.
SourceSheet does contain some formulae linked to other worksheets in SourceBook.xls

I want a VBA routine in a general module (I don't care which workbook the macro resides in), the execution of which will copy SourceSheet and paste it into DestinationBook.xls but with the following odd additional effect:

All locally defined names, and all formulae contained in SourceSheet should be replicated CHARACTER FOR CHARACTER. References to other worksheets in the same workbook should be retained (trust me, the other referenced worksheets with the same name will exist in DestinationBook.xls). However, all references to SouceBook.xls should be stripped from formulae in the pasted SourceSheet and from any defined names that are transported across with it (and all those names must be transported).

Any and all help very gratefully received.

[NickyC]

Please Login or Register  to view this content.

one way to achieve this is to temporarily change all formulas to text strings before you copy. I usually do this by replacing "=" in the source sheet with some character string that will never otherwise appear in the sheet such as "###@@@". Then just reverse the search and replace process when the sheet is copied to turn the text string back to formulas, and it will link to the appropriate names and ranges in the new sheet.

try this macro to do this (run from the sheet you want to copy in sourcesheet):

Please Login or Register  to view this content.

[arlu1201]

Wouldnt it be simpler to just use Save As and make a copy of your file? That way, all named ranges, formulae would remain intact.

[1eyedjack]

@ NickyC

This looks hopeful, but I think would need within the code a routine to detect whether the formula is an array formula, and then preceed the formula with a separate string dedicated to array formulae. Otherwise, when reversing the process, there will be no way for the code to know whether the regenerated formula should be entered as an array.

Do you think I need to worry about the locally defined names that get imported with the SourceSheet?

@ arlu1201

I expect that I am missing something, but having "SavedAs" as suggested, I would then have a lot of work to do importing into the new workbook all of the other data in other worksheets currently stored in DestinationBook.xls. In fact that is what I am doing at the moment, but was hoping for the macro above to cut down on this work.

[1eyedjack]

I had a thought of an alternative approach which I think should give rise to the right outcome:

1) Use the "move or copy sheet" function to take a copy of SourceSheet and place it in DestinationBook.xls.

2) Then employ VBA code to identify every instance in the new copy of SourceSheet that contains within its formulae a reference to an external workbook and strip out from the fomulae any "[" and "]" characters and all characters in between.

3) Then do the same as 2) but to all defined names local to SourceSheet.

Not sure whether it is critical which of steps 2 and 3 to do first, and if so which.

I shall give this a go, but would appreciate help with the code for items 2 and 3 above.

Thanks