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

[Mr2017]

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.

 

[steve the fish]

Something like this but change the target and the destination ranges to suit your situation.

Sheets("Sheet4").Range("A1").Formula = Sheets("Sheet3").Range("A1").Formula

 

[Mr2017]

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:

Sub Formula()

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


End Sub

 

[RoryA]

Workbooks("Book1.xlsx").Sheets("Sheet1")

to specify workbook and sheet names.

 

[Mr2017]

Thank you RoryA and steve the fish.

I strung the two bits of code above together to get something that works!

Thanks again!