Copy and Paste Formula Without the linked file path

Hi, I regularly cut/paste Worksheet cells (with formula's) into a different
Workbook.

I want to keep the formulas "as is" and NOT have the linked workbook name
included in the formula.

Incorrect: ='[MasterFile.xls]Show Approval'!L111

Correct: ='Show Approval'!L111

Is there a paste feature to NO include the linked path in the formula?

Hi!

Try this:

Convert the cell formula that you want to copy into a text
string and copy that over to the other workbook. Once it's
pasted into the other workbook convert it back to a
formula. Then convert the cell that was copied back into a
formula.

To do that you simply edit the formula and precede it with
an apostrophie. Now it's a text string. Delete the
apostrophie and now it's back to being a formula.

This keeps excel from automatically changing the
references in formulas when copying.

Biff

>-----Original Message-----
>Hi, I regularly cut/paste Worksheet cells (with
formula's) into a different
>Workbook.
>
>I want to keep the formulas "as is" and NOT have the
linked workbook name
>included in the formula.
>
>Incorrect: ='[MasterFile.xls]Show Approval'!L111
>
>Correct: ='Show Approval'!L111
>
>Is there a paste feature to NO include the linked path in
the formula?
>.
>

Kevin

Several methods.....

1. F2 to Edit. Precede the formula with an apostrophe then copy and paste to
new workbook. Delete the apostrophe from both cells.

2. F2 to Edit. Copy the formula from the formula bar. Switch to new
workbook and paste into the formula bar(not into the cell).

3. Use a macro to copy the formula(s) as is.

If you want to try 3. post back and I can post the code.


Gord Dibben Excel MVP



On Wed, 19 Jan 2005 17:57:01 -0800, KevinB <[email protected]>
wrote:

>Hi, I regularly cut/paste Worksheet cells (with formula's) into a different
>Workbook.
>
>I want to keep the formulas "as is" and NOT have the linked workbook name
>included in the formula.
>
>Incorrect: ='[MasterFile.xls]Show Approval'!L111
>
>Correct: ='Show Approval'!L111
>
>Is there a paste feature to NO include the linked path in the formula?

Hi,

Thanks for the info re: apostrophe.

I'm cutting and pasting many cells (with formula's) at one time, so putting
apostrophe's would work but it would be very time consuming.

Can you post the Macro code to handle an entire worksheet?

Thanks Gord!!!

"Gord Dibben" wrote:

> Kevin
>
> Several methods.....
>
> 1. F2 to Edit. Precede the formula with an apostrophe then copy and paste to
> new workbook. Delete the apostrophe from both cells.
>
> 2. F2 to Edit. Copy the formula from the formula bar. Switch to new
> workbook and paste into the formula bar(not into the cell).
>
> 3. Use a macro to copy the formula(s) as is.
>
> If you want to try 3. post back and I can post the code.
>
>
> Gord Dibben Excel MVP
>
>
>
> On Wed, 19 Jan 2005 17:57:01 -0800, KevinB <[email protected]>
> wrote:
>
> >Hi, I regularly cut/paste Worksheet cells (with formula's) into a different
> >Workbook.
> >
> >I want to keep the formulas "as is" and NOT have the linked workbook name
> >included in the formula.
> >
> >Incorrect: ='[MasterFile.xls]Show Approval'!L111
> >
> >Correct: ='Show Approval'!L111
> >
> >Is there a paste feature to NO include the linked path in the formula?
>
>

Kevin

Have forgotten where I found this code so cannot give attribution to rightful
creator.

Sub CopyFormulasExact()
Dim rngCopyFrom As Range
Dim rngCopyTo As Range
Dim intColCount As Integer
Dim intRowCount As Integer

' Check that a range is selected
If Not TypeName(Selection) = "Range" Then End
' check that the range has only one area
If Not Selection.Areas.Count = 1 Then
MsgBox "Multiple Selections Not Allowed", vbExclamation
End
End If

' Assign selection to object variable
Set rngCopyFrom = Selection
If Not Selection.HasFormula Then
MsgBox "Cells do not contain formulas"
End
End If

' This is required in case cancel is clicked.
' Type 8 input box returns a range object if OK is
' clicked or False if cancel is clicked. I do not
' know of a way to test for both cases without
' using error trapping
On Error GoTo UserCancelled

' Assign object variable to user-selected cell
Set rngCopyTo = Application.InputBox( _
prompt:="Select the UPPER LEFT CELL of the " _
& "range to which you wish to paste", _
Title:="Copy Range Formulae", Type:=8).Cells(1, 1)

On Error GoTo 0

' Loop through source range assigning any formulae found
' to the equivalent cell of the destination range.
For intColCount = 1 To rngCopyFrom.Columns.Count
For intRowCount = 1 To rngCopyFrom.Rows.Count
If rngCopyFrom.Cells(intRowCount, _
intColCount).HasFormula Then
rngCopyTo.Offset(intRowCount - 1, _
intColCount - 1).Formula = _
rngCopyFrom.Cells(intRowCount, _
intColCount).Formula
End If
Next intRowCount
Next intColCount

UserCancelled:
End Sub


Gord

On Mon, 24 Jan 2005 13:01:03 -0800, KevinB <[email protected]>
wrote:

>Hi,
>
>Thanks for the info re: apostrophe.
>
>I'm cutting and pasting many cells (with formula's) at one time, so putting
>apostrophe's would work but it would be very time consuming.
>
>Can you post the Macro code to handle an entire worksheet?
>
>Thanks Gord!!!
>
>"Gord Dibben" wrote:
>
>> Kevin
>>
>> Several methods.....
>>
>> 1. F2 to Edit. Precede the formula with an apostrophe then copy and paste to
>> new workbook. Delete the apostrophe from both cells.
>>
>> 2. F2 to Edit. Copy the formula from the formula bar. Switch to new
>> workbook and paste into the formula bar(not into the cell).
>>
>> 3. Use a macro to copy the formula(s) as is.
>>
>> If you want to try 3. post back and I can post the code.
>>
>>
>> Gord Dibben Excel MVP
>>
>>
>>
>> On Wed, 19 Jan 2005 17:57:01 -0800, KevinB <[email protected]>
>> wrote:
>>
>> >Hi, I regularly cut/paste Worksheet cells (with formula's) into a different
>> >Workbook.
>> >
>> >I want to keep the formulas "as is" and NOT have the linked workbook name
>> >included in the formula.
>> >
>> >Incorrect: ='[MasterFile.xls]Show Approval'!L111
>> >
>> >Correct: ='Show Approval'!L111
>> >
>> >Is there a paste feature to NO include the linked path in the formula?
>>
>>