Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Posts
    155
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Copying whole worksheets (Excel 2000 SP3)

    I have some complicated models with several worksheets. I want to copy whole worksheets, but if I block copy the contents of a worksheet, and paste it into a new blank, I loose the formatting (such as column widths).

    So I have been using the 'Move /Copy Worksheet' option, but I now have a different problem!

    I have a worksheet which pulls the contents of cells from another named worksheet - lets call it Trial Balance - in the same workbook.

    I want to copy that worksheet into a completely different file/workbook, which also contains a worksheet called Trial Balance. Once I've copied it, I want the worksheet to pull the contents of 'Trial Balance' in the destination workbook, but the action of copying it inserts a reference to Trial Balance in the source workbook into every cell.

    I understand why the program has done this, but can anyone tell me how to switch off this bit of Excel cleverness in this particular case.

    Thanks

    Neil

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Copying whole worksheets (Excel 2000 SP3)

    After doing the copy of the sheet, save the new workbook.
    In that workbook
    Edit links
    change source
    Select the file you are currently working (the file itself)
    [It will remove the "external links" since they are now "internal" links]
    <ok>

    Steve

  3. #3
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying whole worksheets (Excel 2000 SP3)

    This happens to me too. I haven't found a way to 'turn it off'. the work around that i use is to perform a global search and replace, thus ...

    change what?? "c:blahblahblahspreadsheet.xls"
    change to?? "" (ie blank)

    That seems to work for me. It is a good idea to save the file just before you let it do all of them. Also, test it on a few spots.
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Copying whole worksheets (Excel 2000 SP3)

    Don't you mean:
    "c:blahblahblah[spreadsheet.xls]"?

    Not find/replace will not change links due to named ranges. The way I proposed will also "convert" named ranges.

    Steve

  5. #5
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying whole worksheets (Excel 2000 SP3)

    yes - those [ ] always cause trouble. And I didn't know that about the ranged names - links like that are useful but hard to find if you don't know about them.

    Reminds my of that saying ...
    Q. "Where did you find your keys?"
    A. "The last place I looked."
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •