Page 1 of 2 12 LastLast
Results 1 to 10 of 11

How to paste formula to new workbook without linking

This is a discussion on How to paste formula to new workbook without linking within the Excel Questions forums, part of the Question Forums category; Often one of my array formula's gets deleted from a sheet. If I copy and paste from a backup xls ...

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Posts
    57

    Default

    Often one of my array formula's gets deleted from a sheet. If I copy and paste from a backup xls file links are created in the pasted formula. All I want is the exact same formula. How can I do it?

    Thanks in advance

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582

    Default

    Hi mikeyboy

    Haven't tested this, but should work. Just change names and cells to suite

    Code:
    Sub DoIt()
    Sheet1.Range("A1").FormulaArray = _
    Workbooks("Book2.xls").Sheets(1).Range("A1").FormulaArray
    
    End Sub
    Another non VBA method is to copy the formula from the Formula bar, select the destination cell, then paste into the formula bar.

    You may also find this link of use:
    http://www.ozgrid.com/Excel/TipsAndTricks.htm

    Scroll down to:
    "How to copy formulas without the reference changing"

    _________________
    Regards
    Dave Hawley
    Xl Add-ins, with free File Size Reducer
    40+ more here
    Xl Training

    [ This Message was edited by: Dave Hawley on 2002-07-23 04:10 ]

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    Midlands, UK
    Posts
    217

    Default

    Have you tried highlighting the formula in the Formula Bar, hit Ctrl + C, activate the cell you want to paste into ( ie double click in it ), hit Ctrl + V.

    This should copy the array formala as text, rahter than a formula, thus preventing links being created back to the backup file.

    Iain

  4. #4
    Board Regular
    Join Date
    Apr 2002
    Location
    Midlands, UK
    Posts
    217

    Default

    managed to post the same message 3 times.

    [ This Message was edited by: Iain Lewis on 2002-07-23 04:12 ]

  5. #5
    Board Regular
    Join Date
    Apr 2002
    Location
    Midlands, UK
    Posts
    217

    Default

    Have you tried highlighting the formula in the Formula Bar, hit Ctrl + C, activate the cell you want to paste into ( ie double click in it ), hit Ctrl + V.

    This should copy the array formala as text, rahter than a formula, thus preventing links being created back to the backup file.

    Iain

  6. #6
    Board Regular
    Join Date
    Apr 2002
    Posts
    57

    Default

    Thanks, looks like I'm all set!

  7. #7
    klb
    klb is offline
    Board Regular
    Join Date
    Apr 2002
    Location
    Minnesota
    Posts
    821

    Default

    Yet another option is to change the cell you want copied to text by putting a ' at the start of the formula. Then copy, paste and edit your destination cell to remove the ' and change format back to number.

    You will have to go back and undo your change in your original spreadsheet or simply close it without saving.


    Curiosity may have killed the cat but...
    satisfaction brought it back. >^;^<

  8. #8
    New Member
    Join Date
    Jan 2010
    Posts
    2

    Default Re: How to paste formula to new workbook without linking

    I LOVE THIS FORUM!!

  9. #9
    New Member
    Join Date
    Feb 2012
    Posts
    1

    Default Re: How to paste formula to new workbook without linking

    If you want a non-VBA method to accomplish this with many cells simultaneously, first copy all your cells with a simple copy and paste. Bring up the "find and replace" dialogue. Enter the link portion of the formula in to the "find" field and make sure there is nothing in the "replace" field. Click "replace all". You should be left with just the original formula in all the fields.

  10. #10
    New Member
    Join Date
    Jul 2012
    Posts
    3

    Default Re: How to paste formula to new workbook without linking

    Quote Originally Posted by JScotty View Post
    If you want a non-VBA method to accomplish this with many cells simultaneously, first copy all your cells with a simple copy and paste. Bring up the "find and replace" dialogue. Enter the link portion of the formula in to the "find" field and make sure there is nothing in the "replace" field. Click "replace all". You should be left with just the original formula in all the fields.
    Thanks a lot, simplest methods always best Now, a bigger challenge - how do I do the same trick, but with ca 900 files, at the same time? Is there a simple VBA macro to do it? Thanks a lot.

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