How to copy a formula without changing references
How to copy a formula without changing references
(OP)
Hav you ever wanted to copy a formula from one cell to another without changing the referenced cells?
Heres a small pices of code that can do that:
Dim myvalue As String
Sub move_formula1()
myvalue = ActiveCell.Formula
End Sub
Sub move_formula2()
ActiveCell.Value = myvalue
End Sub
Link to two subs to keys on the keyboard (eg: q and w) and you will have this little function at your fingertips!
-Be aware crtl+q/w might be linked to functions that you frequently (i dont).
Best Regards
Morten
Heres a small pices of code that can do that:
Dim myvalue As String
Sub move_formula1()
myvalue = ActiveCell.Formula
End Sub
Sub move_formula2()
ActiveCell.Value = myvalue
End Sub
Link to two subs to keys on the keyboard (eg: q and w) and you will have this little function at your fingertips!
-Be aware crtl+q/w might be linked to functions that you frequently (i dont).
Best Regards
Morten
RE: How to copy a formula without changing references
prex
motori@xcalcsREMOVE.com
http://www.xcalcs.com
Online tools for structural design
RE: How to copy a formula without changing references
Best Regards
Morten
RE: How to copy a formula without changing references
jproj
RE: How to copy a formula without changing references
Best Regards
Morten
RE: How to copy a formula without changing references
I never noticed that you can Copy and Paste including the equal sign: sometimes you take an habit and continue using it over the years, without noticing the shorter path.
To Morten: of course the problem you raised is quite common and needs a simple solution, but the solution of jproj is the simplest way for me. By the way you don't have to copy the cell content (in this case the references are modified), but you must select (highlight) the formula in the formula bar, then copy and finally paste in the destination.
prex
motori@xcalcsREMOVE.com
http://www.xcalcs.com
Online tools for structural design
RE: How to copy a formula without changing references
RE: How to copy a formula without changing references
Another Quattro Pro advantage was that a copied cell stayed on the clipboard until something else was copied. So you could paste without still having that stupid dotted line around the copied cell(s). You could even copy a cell, delete it, and then hit paste somewhere else. Try doing that in Excel with any efficiency.
I've heard you could get around this by that clipboard (of the last 12? copies) that pops up ocassionally, but like that stupid "help" Paperclip in the lower right corner (that I uninstall on everything I use), I find it not to be useful enough or worth my time to figure out how to use.
Sorry for the OT (off-topic) and ranting,
Ken
RE: How to copy a formula without changing references
sc
RE: How to copy a formula without changing references
Anyway event though i seem to be the only one who finds this usefull i improved it slightly (yes it IS possible
Dim myvalue As Variant
Sub move_formula1()
myvalue = Selection.Formula
End Sub
Sub move_formula2()
Selection.Value = myvalue
End Sub
Using selection instaed of activecell makes it possible to copy ranges. But you have to select a range also when "pasting"
Best Regards
Morten
RE: How to copy a formula without changing references
To add $, you can use "F4" key, it will save you time.
Before you add "$", place the sursor where you want to add.
Regards,
jackzhong
RE: How to copy a formula without changing references
Defining Names also really helps with debugging later...
Profit = Revenue-Cost, is a lot easier to understand than F4=E3-B2
RE: How to copy a formula without changing references
copying the cell (not its contents) and using paste special and selecting formulas.
RE: How to copy a formula without changing references
Thanks, guys.
RE: How to copy a formula without changing references
RE: How to copy a formula without changing references
RE: How to copy a formula without changing references
To copy to a different cell in the same row, copy upward or downward through any number of empty cells, Move horizontally to the new column, Copy up or down to the original row, etc. I know this is only of use in certain cases, but hope it helps.
RE: How to copy a formula without changing references
1. copy the original sheet
2. drag the formula cell to the new position, on the copy, that you require.
3. copy the formula back to the original sheet
4. delete the copy.
RE: How to copy a formula without changing references
hit ctrl-` (ctrl ~) to switch to "equations" view
select and copy the equations
paste them into notepad
you can switch back into "results" view at this point, if you like, by hitting ctrl-` again
ctrl-a to select all in notepad, then ctrl-c to copy
paste into excel, wherever you like.
This has the same effect as copying the text from the formula bar, but it works on multiple cells at the same time.
RE: How to copy a formula without changing references
Interesting shortcuts. I could only make one cell in a selected range change to the formula view with [ctrl]+'
This can be accessed more easily by pressing F2 (Same keystroke allows you to rename files in Windows Explorer and various other programs)
What I did find was that [ctrl]+# changes a selected set of cells to date format and [ctrl]+@ changes them to time format. [ctrl]+~ doesn't seem to do anything on my version of Excel.
Also, as a totally unexpected shortcut key, [alt gr]+F2 opens the open file window (Same as [ctrl]+o ) - I found this accidentally while working in Word as you can change the format of a heading to Heading level by holding down [alt gr] and pressing the number of the heading level required.
Is there a list of shortcuts available for Excel? Very few of them are written by their menu items, so a lot of the time I use the underlined letters in menu items to select them instead [alt]+oe to format cells is probably the shortcut I use most.
Another interesting one is that [ctrl]+r will copy the cell to the left of the selected cell into the selected cell. I only found this one because [ctrl]+l, r and e in Word allow you to left, right and centre align text.
RE: How to copy a formula without changing references
' and ` keys when I said ctrl ~ . ~ is shift + `, of course. The eqn-view shortcut is accessed via ctrl ` in excel 97 and 2000; I'm not sure about other versions.