Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Jun 2004
    Location
    Itasca, Illinois, USA
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pasting Formulas into Different Workbooks (Excel 2003)

    I am trying to issue formula fixes in an Excel workbook to multiple users. The fixes include formula references to different sheets in this workbook. I want to copy the fix formula from my workbook to the user's workbook and have the formula reference the sheet with the same name in their own workbook. Currently, when the formulas are copied they want to append my filename to the sheet reference.

    (e.g. The formula should be (A1*'Table of Variables'!$B$2). When this is copied to the new workbook it will be (A1*'[My Workbook.xls]Table of Variables'!$B$2). It does this with and without the absolute reference.

    Thanks.

  2. #2
    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: Pasting Formulas into Different Workbooks (Excel 2003)

    Is this a manual process or are you looking for a macro driven one?

    Either way, you could always delete the [myworkbook] part. The other option is that you highlight the formula from the formula bar and copy that (less the '=' sign). Then when you get to where you want to pasting it, insert the '=' sign and the past the rest. It is sort of like copying a string instead of a formula.
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

  3. #3
    New Lounger
    Join Date
    Jun 2004
    Location
    Itasca, Illinois, USA
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pasting Formulas into Different Workbooks (Excel 2003)

    Thanks. I was hoping to make this as automated as possible since the formula will be copied into many cells and I didn't want the users to have to go through that process. I believe that the best approach would be to copy the corrected section into the user's file and use Edit Links to change the source from the fix file to the user's file.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pasting Formulas into Different Workbooks (Excel 2003)

    If you are copying the formula manually, then you can do the following:

    1- Select the cell containing the formula.

    2- Hilight the formula in the formula bar and select Copy from the Edit menu or right click and select copy.

    3- Select the cell where the formula is to be pasted. If the formula bar contains anything, highlight it, if the formula bar does not contain anything just click in to put the cursor there. Select Paste from the Edit menu or right click and select Paste.

    That should paste the unmodified formula.

    You could also write VBA code to automate this, but we would need a much more detailed description. We would need to know how to recognize the source and target workbooks, how to recognize where the formulas that need to be copied are, and how to know where to paste them.
    Legare Coleman

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Pasting Formulas into Different Workbooks (Excel 2003)

    Couldn't you do this with some Find-&-Replace macros from the recorder, carefully designed as to the Find text and Replace text?
    -John ... I float in liquid gardens
    UTC -7DS

  6. #6
    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: Pasting Formulas into Different Workbooks (Excel 2003)

    What I have done in the past is just paste in the new formula (not worrying about the [oldworkbook] part) and then when i have copied all of them, do a global find and replace on:

    find "[oldworkbook]"
    replace with ""
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

  7. #7
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pasting Formulas into Different Workbooks (Exc

    You might like to try playing with the following code - which was written to deal with a slightly similar problem. In my case, the links were to be changed from one month's files to another. (The files in question were held on an R: drive.) <pre>Sub ChangeLinks()
    Dim strOldName, strNewName, strOldNumber, strNewNumber As String
    strOldNumber = InputBox("Enter current month number for template", "Old Month")
    strNewNumber = InputBox("Enter new month number for template", "New Month")
    strOldName = MonthName(CInt(strOldNumber))
    strNewName = MonthName(CInt(strNewNumber))
    If strOldNumber < 10 Then 'adjust month to two digits
    strOldNumber = "0" & strOldNumber
    End If
    If strNewNumber < 10 Then 'adjust month to two digits
    strNewNumber = "0" & strNewNumber
    End If
    Cells.Replace _
    What:="R:", Replacement:="''R:", LookAt:=xlPart, _
    SearchOrder:=xlByColumns, MatchCase:=False
    Cells.Replace _
    What:=strOldName, Replacement:=strNewName, LookAt:=xlPart, _
    SearchOrder:=xlByColumns, MatchCase:=False
    Cells.Replace _
    What:=strOldNumber, Replacement:=strNewNumber, LookAt:=xlPart, _
    SearchOrder:=xlByColumns, MatchCase:=False
    Cells.Replace _
    What:="'''R:", Replacement:="'R:", LookAt:=xlPart, _
    SearchOrder:=xlByColumns, MatchCase:=False
    End Sub</pre>

    HTH
    Gre

Posting Permissions

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