News

Loading...

Friday, September 07, 2007

Another Microsoft Question

OK kids, there's a five spot for whoever can answer this first (payable in Nashville): I have an Excel document with several sheets. Right now one of the sheets points to an earlier one; i.e. there are lots of cells in it that say stuff like "'May budget'!C6".

Now I want to copy the entire sheet into a different version of this same document. I.e. the sheets and their names etc. are all identical. The problem is, when I copy and paste the sheet from my version onto the "same" sheet in the other document, Excel quite understandably is still linking those cells to the May budget sheet in the first document. I.e. I want to paste it in such that the cells point to the 'May budget'! in the same file.

Any thoughts?

6 comments:

  1. I usually handle this by moving BOTH sheets over and then updating the "May budget" sheet with the revised data. If that's not practical, though, you'll need to do a Find-Replace, where the find is the filename string with the appropriate bracketing/quoting, and the Replace field is left blank.

    ReplyDelete
  2. Another way to do this is by using a macro to loop through every cell of the destination worksheet, replacing the formula of each cell with the formula from the corresponding cell of the source worksheet.

    Here is an an Excel workbook with the code to do that as a userform that makes it as easy as copying a sheet from one workbook to another. If you have Excel 2007, the button to lauch the userform will on the "Add-Ins" tab. If you have Excel 2003 or earlier, it will appear on a custom toolbar.

    You may have to adjust your macro security settings in order for macros to run. Since the code is unsigned and I am not a trusted source, you should probably examine the code first to make sure it's not doing anything malicious.

    ReplyDelete
  3. After pasting the data into the 2nd workbook, select Edit | Links from the menu. This will let you change all references pointing to the old book to now point to the new one.

    ReplyDelete
  4. Note on the above: you need to save the 2nd book before editing the link, so that you can navigate to it in the file dialog.

    ReplyDelete
  5. Thanks guys. I think I'll try your suggestions in order of 3, 1, and 2 (since macros scare me).

    ReplyDelete
  6. Anonymous12:23 AM

    Welcome to our game world, wakfu kamas , wakfu gold , buy wakfu kamas , wakfu money and wakfu kama , they are very interesting.

    ReplyDelete