Pasting Formulas into Different Workbooks (Excel 2003)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Couldn’t you do this with some Find-&-Replace macros from the recorder, carefully designed as to the Find text and Replace text?

Reply | Quote

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 “”

Reply | Quote

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 “”

Reply | Quote

Couldn’t you do this with some Find-&-Replace macros from the recorder, carefully designed as to the Find text and Replace text?

Reply | Quote

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.)

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

HTH

Reply | Quote

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.)

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

HTH

Reply | Quote

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.

Reply | Quote