How to copy formula text ONLY from a cell then paste it into a cell in another workbook?

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
644
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi

I'd like to copy the text of a formula in a cell, then paste that text into a cell in a different workbook, without the formula changing.

I can't use 'copy' then paste special xl paste formulas, because the reference changes when the formula is pasted into the other workbook.

What would be the best way of doing this?

To provide a simple example, let's say

cell A1 has the formula =C1+C2 in Sheet 1 of Book 1 (a new workbook).

if you copy cell A1 in Book 1, then paste that formula into cell D5 of Book 2 (another new worbook), the formula changes to "=F5+F6"

However, I need to be able to transfer the formula as it is, because Book 2 doesn't have the formula but it needs to be inserted into that book during an import process.

The real formula is a lot longer (over 60 arguments).

Please let me know if you'd like me to clarify the question further, thanks.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Something like this but change the target and the destination ranges to suit your situation.

Code:
Sheets("Sheet4").Range("A1").Formula = Sheets("Sheet3").Range("A1").Formula
 
Upvote 0
Hi Steve

Thanks for the response.

I'd be really grateful if you could clarify how I should include the workbook names here.

I normally use the "back of the house" names for Sheets rather than the Sheet "names," so I'm not used to using the code style that you've used.

I wrote this Sub, using your code - if you could clarify where / how the Book 1 and Book 2 workbooks should be inserted into this, it would be greatly appreciated:

Code:
Sub Formula()

Sheets("Sheet1").Range("D5").Formula = Sheets("Sheet1").Range("A1").Formula


End Sub
 
Upvote 0
Code:
Workbooks("Book1.xlsx").Sheets("Sheet1")

to specify workbook and sheet names.
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top