Page 1 of 2 12 LastLast
Results 1 to 10 of 12
Like Tree1Likes

Copying sheets to another workbook-without links

This is a discussion on Copying sheets to another workbook-without links within the Excel Questions forums, part of the Question Forums category; How do I copy a sheet to another workbook without the link to the original and still maintain the formula?...

  1. #1
    New Member
    Join Date
    Nov 2002
    Posts
    5

    Default

    How do I copy a sheet to another workbook without the link to the original and still maintain the formula?

  2. #2
    Board Regular
    Join Date
    May 2002
    Location
    Ipswich, Suffolk, England
    Posts
    135

    Default

    Is that not the Paste special formula function ?

  3. #3
    New Member
    Join Date
    Nov 2002
    Posts
    5

    Default

    Yes, that is it, but I would like to paste multiple sheets at a time, and there is no option for paste special when copying sheets from one workbook to another. Any ideas?

  4. #4
    Board Regular
    Join Date
    May 2002
    Location
    Ipswich, Suffolk, England
    Posts
    135

    Default

    How many sheets ? and what sort of volume of repitition are we talking?

  5. #5
    New Member
    Join Date
    Nov 2002
    Posts
    5

    Default

    There are a total of 10 sheets, which get information for the formulas from other sheets in the same workbook. I have 250 workbooks to create these identical 10 sheets in. All workbooks are of the same format (the data is on the same sheet label, same cell identifier, just different data). When I copy them to another workbook, the links are posted in the formulas. I would like the sheets to copy over without the links and start using the identified cell and sheet as listed in the original formula. Thanks for your help!

  6. #6
    New Member
    Join Date
    Oct 2002
    Location
    Toronto
    Posts
    39

    Default

    Try defining your old and new filenames, copy-paste the sheet, then add a variation of this to your macro: (use record-macro to get the syntax right)

    ActiveWorkbook.ChangeLink Name:="oldfilename.xls", NewName:= _
    "\foldernamenewfilename.xls", Type:=xlExcelLinks

  7. #7
    New Member
    Join Date
    Nov 2002
    Posts
    5

    Default

    That would work, but the sheets are rather large and contain many formulas, is there a way to just specify "remove links" from the copied sheet?

  8. #8
    New Member
    Join Date
    Oct 2002
    Location
    Toronto
    Posts
    39

    Default

    I wish it was that easy. ;o) If you want to do it manually then Edit-Links-Change Source and replace with the new (active workbook) filename.

  9. #9
    New Member
    Join Date
    Nov 2002
    Posts
    5

    Default

    Thanks, I will try that.

  10. #10
    New Member
    Join Date
    Sep 2002
    Location
    London
    Posts
    10

    Default

    Open both workbooks.....right click on the sheet tabs you want to copy and choose a workbook to paste them into. Make sure you tick the create a copy checkbox or you'll move the original.

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com