How to make an *EXACT* copy of an excel sheet?

[jsteph]

Hi all,
I'm sure this is easy...but my issue is this: I have a workbook with several dozen sheets on it. Lots of formulas and code and stuff hooked to this sheet and that sheet, etc., but all within the same workbook. (Excel 2007)

I have another coder updating just *one* of the sheets on his copy of this workbook. When I attempt to copy his changes to my copy, (after deleing the corresponding sheet in mine), all the formulas are prefixed with the entire path\filename of his sheet.

So...I really don't want Microsoft to try and *help* me by assuming I want everything in my sheet to point back to this temporary copy. And there are so many formulas that I can't re-do them all. How do I get it to make an *exact* copy, word for word, formulas and all, of that sheet so everything can work right?

Thanks,
--Jim

 

[osx99]

This isn't really a VBA topic or solution

The easiest way is to do exactly what you have done already. Then highlight all cells and use Find and Replace All (Ctrl F) to replace the path across all cells

 

[Gruuuu]

Actually, this can be accomplished manually or with VBA.

All you need to do is change links from the old workbook to the current.

If you're content with doing this manually, go for it!
If not, I suggest checking out the reference for Changelinks
The quick and dirty example they give:

ActiveWorkbook.ChangeLink "c:\excel\book1.xls", _
    "c:\excel\book2.xls", xlExcelLinks

That will probably work for your purposes. If his workbook name changes every time, you'll have to check out the LinkSources method.
Good Luck!

 

[jsteph]

Thanks, I will give those a try.

But isn't there a 'best practice' for this? I can't be the only one who maintains two copys of a file and makes changes in one and copys changes from these individual sheets to another workbook.
--Jim

 

[Gavona]

Without VBA, the equivalent of Gruuu's suggestion is (in Excel 2003) Edit, Links, [select the linked file then] ChangeSource and point to your file.

Gavin