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

Mr2017

Active Member
Joined
Nov 28, 2016
Messages
463
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.
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,072
Office Version
365
Platform
Windows
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
 

Mr2017

Active Member
Joined
Nov 28, 2016
Messages
463
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
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,966
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Code:
Workbooks("Book1.xlsx").Sheets("Sheet1")
to specify workbook and sheet names.
 

Watch MrExcel Video

Forum statistics

Threads
1,098,974
Messages
5,465,757
Members
406,449
Latest member
cubeincube

This Week's Hot Topics

Top