In Excel, if you want to copy and paste a formula exactly as it is, you generally need to add absolute references to that formula, e.g. change "=A1+B1" to "=$A$1+$B$1" and then copy and paste.
However, if you don't want to add absolute references to the formula in question, and simply want to copy and paste "=A1+B1" as "=A1+B1", Excel doesn't really offer any solution that I'm aware of, besides manually copying and pasting the formula in question, which can get tedious.
So I'm wondering if there's a good way to use Keyboard Maestro to do this.
The tricky thing is that this would need to work not only for copying a standalone cell, but would also need to work for copying ranges, too. That is, if all you wanted to do was copy a formula exactly as is from one cell to another, that macro would be pretty simple. But this would also need to work if you wanted to copy and paste a range of cells containing formulas from one row to another, from one column to another, etc. while keeping all the formulas unchanged. That's where I get hung up.
I'm wondering if anyone here might have a solution. Preferably one that avoids VBA! And that avoids manual steps/workarounds.
I had the same problem and came across a way of doing this today. Select the cells that you want to copy and then the macro turns on Show Formula mode, copies the cells, and sets them as plain text to be pasted where you want. Once you paste into the new location, hit CTRL+ ` again and it will go out of formula mode, or you can use the second macro to do it in one click. You may need to copy and paste the formats from the original cells, but that should be quick. There may be a more elegant way of doing this, but seems to work for what I need it to, so thought I'd share.
I've played around with your approach before, but only by testing in manually. When you try it manually, the cell references still adjust.
Why do you think it is that these macros don't run into this problem? Does it have something to do with what you're doing with the "Cell_Reference" variable?
Hi Alex, thanks for checking out the macro! I think it has to do with the set system clipboard to plain text action. What I was copying with the macro was basically doing the method manually by turning on formula mode, copying the cells, pasting into a text editor like Notes or TextEdit, re-copying the cells from the text editor, and then pasting back into excel. The macro just does this all in one step, so I don’t have to go back-and-forth between apps
Got it. That makes sense. I'll have to play around with it myself some, but just curious, have you run into any issues when copying multiple cells? Say, a 1x4 or 4x1 or 4x4 array? They all work?
Nope, seems to work for everything I’ve tried it on, but I can’t say I’ve done exhaustive testing on it or anything. They’re could be some situations where it doesn’t work
Only tweak I made was moving the second "^`" keystroke to the end of the copy macro. That way, I don't need a separate macro for the pasting step, and can just use normal Excel pasting commands. (Though there might be a reason that you set things up the way you did.)
Thanks again for sharing this. It solves a long-standing issue I've had with Excel.
That’s a good idea actually. I think there was a reasoning there, but I can’t really remember what it was now, so I may change that myself. Glad it’s working for you! It’s been an annoyance for me for a while as well.
So adding the second "^`" worked for me, but only if I added a .3 second delay first. Otherwise, it wouldn't copy the cells in time before it closed, so there wasn't anything to paste. That must have been why I separated it originally
