Copying Formula's from one Workbook to another 2

[Suggie]

I have a workbook called 'Incoming EB Process'

There are two worksheets called Process and Dept MIS

I want to copy a formula in the Process worksheet that links to the Dept MIS worksheet and paste the formula into another workbook. The formula within the first workbook reads:

='Dept MIS'!G2

When I copy it into the second workbook it reads:

='[Incoming EB Process.xls]Dept MIS'!G2

Is there a way just to copy the formula that it only pastes
the formula to read as ='Dept MIS'!G2

reason is that in the second workbook I already have the worksheet Dept MIS and want it to link to this.

TIA,

 

[xlbo]

just do a find and replace in formulae for:
[Incoming EB Process.xls]
replace with ""

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[Suggie]

Thanks Geoff, I did do that, was wondering if there was another way, trying to be too clever....and failing!

 

[xlbo]

not really, no I'm afraid

You could also use Edit>Links>Break links but there is no direct way of doing something like this

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[PeterMoran]

Hi all,

Most suprised at xlbo saying there is something you can't do in Excel!!

Try this macro, which does what is requested:

Sub CopyAsText()
' CopyAsText Macro Installed Oct 99
' Copy to Clipboard as Text
' The DataObject variable type is defined in the Forms library, so
' you 'll need a reference to that library. (In the VBA Editor, go to
' the Tools menu, References item, and put a check next to "Microsoft
' Forms 2.0 Object Library".
'
Dim DataObj As New DataObject
DataObj.SetText ActiveCell.Formula
DataObj.PutInClipboard
End Sub

I assume "Microsoft Forms 2.0 Object Library" is still available or an appropriate equivalent.

I have it assigned to a button and use it lots! It is very handy for just this situation. The only limitation is that you can only copy one cell at a time.

Also it is part of ASAP Utilities (

http://www.asap-utilities.com)

which is a fantastic free add-in for Excel.

Good Luck!
Peter Moran

 

[xlbo]

Well - you can't do it in native excel and you still can't do more than 1 cell at a time ;-)

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[TonyJollans]

Hi Suggie,

Don't know if this will help, but it depends a bit on what you want to happen to the cell reference in your formula.

If you copy cell A1 from the first book to A1 in the second book, you get ='[Incoming EB Process.xls]Dept MIS'!G2 as you say. If you copy it to cell B1 in the second book, because it is a relative reference, you will get [blue]='[Incoming EB Process.xls]Dept MIS'![highlight]H2[/highlight][/blue].

Now there is nothing I know that will allow you to do as you want with a relative reference, but if you have an absolute reference you could copy it as TEXT instead of as a cell. Select your cell and select and copy the text from the formula bar; then go and paste as normal in your second book. You won't get the added workbook reference, but you also won't get the relative cell adjustment.

Also, as Geoff points out, you can only do one cell at a time. You pays yer money, you takes yer choice!

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[duke63]

Try This:
In the source worksheet-
Select the range of formulas you want to copy.
Use "Edit-Replace"
Search for "=" Replace with "a=" (replace all in range)-Formula is now text

Copy and paste to new workbook
then
Search for "a=" Replace with "=" (replace all in range)

(make sure you have a range of more than one cell selected or you will place an "a" in front of all your formulas)

 

[KenWright]

You don't say what version you are using, but if you have the option of Edit / Links / Change Source (I think it's 2000 or later), then just change the source to the workbook the links are in and they will all disappear.

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[TonyJollans]

Hi Ken,

Nice one! Star for that.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[KenWright]

Cheers Tony

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[iGrant]

Try this formula in the Process worksheet:

=indirect("'Dept MIS'!G2")

When you copy and paste this into another workbook, it will refer to cell G2 of that workbook's sheet 'Dept MIS'