Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
ron
 
Posts: n/a
Default paste formulas between workbooks without workbook link

Hello,
I want to copy and paste several cells, each containing
formulas, from one workbook to another. The formulas
contain references to several different sheets and I want
the paste to contain the formulas exactly as they are in
the originating workbook. When I paste these cells into
the destination workbook, however, the formulas have
inserted in them links to the originating workbook file
name.

The only way I can see to solve this problem is to
individually select each cell, copy its formula from the
edit formula field and then paste it into the edit formula
field in the destination workbook. There are a lot of
cells to copy and paste, so doing this cell by cell is not
very feasible. Does anyone have any suggestions? Is there
an option in Excel to disable the workbook link appearing
in the formulas in the target workbook?

thanks
Ron

  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: paste formulas between workbooks without workbook link

Hi Ron,

I understand that you want to copy and paste cells with formulas from one workbook to another without having the workbook link appear in the formulas in the target workbook. I can definitely help you with that.

One way to achieve this is by using the "Paste Special" feature in Excel. Here are the steps:
  1. Select the cells containing the formulas that you want to copy.
  2. Right-click on the selected cells and choose "Copy" or press "Ctrl+C".
  3. Go to the destination workbook and select the cell where you want to paste the formulas.
  4. Right-click on the selected cell and choose "Paste Special" or press "Ctrl+Alt+V".
  5. In the "Paste Special" dialog box, select "Formulas" and uncheck the "Paste link" option.
  6. Click "OK" to paste the formulas without the workbook link.

This method will copy and paste the formulas exactly as they are in the originating workbook, without any links to the original file. You can also use the keyboard shortcut "Ctrl+Shift+V" to open the "Paste Special" dialog box directly.

Formula:
// No macro or Visual Basic code found in the text. 
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

Here is a way, maybe not very elegeant but working.
First in the source sheet, select the sheet, do editreplace and in find
what put equal sign =, then replace with use for instance ^^ (something that
you are sure is not in the worksheet), then copy everything to the new
workbook, then in the new workbook reverse replacement with = replacing ^^,
then finally reverse replacement in the source workbook


Regards,

Peo Sjoblom

"ron" wrote:

Hello,
I want to copy and paste several cells, each containing
formulas, from one workbook to another. The formulas
contain references to several different sheets and I want
the paste to contain the formulas exactly as they are in
the originating workbook. When I paste these cells into
the destination workbook, however, the formulas have
inserted in them links to the originating workbook file
name.

The only way I can see to solve this problem is to
individually select each cell, copy its formula from the
edit formula field and then paste it into the edit formula
field in the destination workbook. There are a lot of
cells to copy and paste, so doing this cell by cell is not
very feasible. Does anyone have any suggestions? Is there
an option in Excel to disable the workbook link appearing
in the formulas in the target workbook?

thanks
Ron


  #4   Report Post  
Dave Peterson
 
Posts: n/a
Default

I select my range to copy
edit|replace
what: = (equal sign)
with: $$$$$= (some unique string)
replace all

Now my formulas are just text.

Then copy|paste

Then clean up both ranges:
select the range
edit|replace
what: $$$$$= (that same unique string)
with: = (equal sign)
replace all

Now my text strings are formulas again.

(Make sure you have worksheets in that new workbook that match up--else you'll
be dismissing lots of dialogs!)

I'd fix one formula manually just to test.

===
Alternatively, copy and paste like you did
and then edit|links|change source
may work nicely for you.

ron wrote:

Hello,
I want to copy and paste several cells, each containing
formulas, from one workbook to another. The formulas
contain references to several different sheets and I want
the paste to contain the formulas exactly as they are in
the originating workbook. When I paste these cells into
the destination workbook, however, the formulas have
inserted in them links to the originating workbook file
name.

The only way I can see to solve this problem is to
individually select each cell, copy its formula from the
edit formula field and then paste it into the edit formula
field in the destination workbook. There are a lot of
cells to copy and paste, so doing this cell by cell is not
very feasible. Does anyone have any suggestions? Is there
an option in Excel to disable the workbook link appearing
in the formulas in the target workbook?

thanks
Ron


--

Dave Peterson
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can't Copy and Paste or Paste Special between Excel Workbooks wllee Excel Discussion (Misc queries) 5 April 29th 23 03:43 AM
Copy worksheets with formulas between different workbooks Tim Excel Discussion (Misc queries) 3 March 31st 05 12:40 PM
Can't Copy and Paste between Excel 2003 Workbooks wllee Excel Discussion (Misc queries) 6 March 30th 05 02:59 PM
Linking Workbooks Dede McEachern Excel Worksheet Functions 0 January 21st 05 09:27 PM
calculating formulas for all workbooks in a folder Chad Excel Worksheet Functions 3 November 13th 04 06:22 PM


All times are GMT +1. The time now is 01:32 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"