How to copy a formula without changing references 2

[MortenA]

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

 

[prex]

There's a simpler approach: select the formula excluding the initial '=' and Copy it, then go to the destination cell, type '=' and then Paste it. prex
motori@xcalcsREMOVE.com

http://www.xcalcs.com

Online tools for structural design

 

[MortenA]

well thats exactly what i dont find "simple" ;-)

Best Regards

Morten

 

[jproj]

Actually, you can copy the equals sign with the cell references in the original equation, then click on the target cell and paste it directly (no need to press "=" then paste it... takes less than 5 seconds).

jproj

 

[MortenA]

yes of course :-( but then the references in the formula will change! Thats what i tried to solve. Maybe its just me - but on occation i have to "move"/"copy" a formula without changin the references when moved.

Best Regards

Morten

 

[prex]

Thanks to jproj!
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

 

[MortenA]

OK with me

 

[brengine]

Too bad we're all pigeon-holed into using Excel. In the old days, with Quattro Pro you could cut a cell and then paste it in multiple locations. Any other cells referencing the cut cell will all be converted to look at wherever the first paste was. So I would cut it, paste it in the same place to maintain any other references, and then paste it in your other location maintaining the references in that cell. I consider that pretty efficient with the [ctrl]-[x] and [ctrl]-[v] keystrokes.

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

 

[sc]

Or you could edit the cell you want to copy and place the cursor where the cell ref is that you want to keep, then hit the F4 key and it will place a $ symbol with your cell ref. Works quite well if you need to keep some cell refs and not others in the one formula.


sc

 

[MortenA]

Of course the $ works fine but if you havnt got it in there from then beginning then its tiresome to add $$$ (UNLESS YOU GET $$$ BY THE HOUR

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

 

[jackzhong]

MortenA,

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

 

[AKVecoPE]

Another option would be to name the cells you wish to refer to. Since Excel defines Names with $ signs, copy and paste works.
Defining Names also really helps with debugging later...

Profit = Revenue-Cost, is a lot easier to understand than F4=E3-B2

 

[Hayden]

I know this may now be a bit late but you can alklso do it by:

copying the cell (not its contents) and using paste special and selecting formulas.

 

[trainguy]

I can't believe I never thought of copying the contents of text in the formula bar, until you guys thought of it for me. It's so simple, it's brilliant.

Thanks, guys.

 

I think the best way to do this (for many cells) is copy from the cells and then paste special -> paste link.

 

[RaulPE]

prex and jproj: OK for your tips!! Sorry for the delay, but I just found this site yesterday!!

 

[Lcubed]

To copy to a different cell in the same column, you can Copy, dragging with the +cursor, through any number of empty cells to the right or left, then Move any distance up or down, then Copy back to the original column, then delete the extraneous cells to which you have copied the formula. The reference cells will be unchanged.

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.

 

[Buffo]

Another alternative I have used in the past is to
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.

 

[ivymike]

hmm... I thought this had been mentioned before, but in re-reading I didn't find it.

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.

 

[peglor]

Hmmm.

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.