How to "clone" or "duplicate" cells & their formulas...

T

Tripp Knightly

Copy-Paste-Special lets you do some useful things in Excel.

But there's no quick & easy way to "clone" a cell and make an exact
duplicate of it. Often I do this either because a given cell is a
good starting point for a different calculation.

The workarounds work, but, like workarounds, they kinda stink. For
example, you can edit the formula, put a quote in front of it & turn
it into a string, then copy it to wherever you need it, and then
finally edit it (and the original) back to formula. Or, you can use
absolute references in the formula and then copy the formula. But
it's a pain to toggle all the references and toggle them back.

Am I missing something, or is there no easy way to clone a cell?
 
A

Arvi Laanemets

Hi

I often use copying from formula toolbar, then pressing Esc, and then
selecting the target cell and pasting into formula toolbar again.
Of-course when I want to copy cell format too, I have to do this separately
(using PasteSpecial).
 
T

Tripp Knightly

Yeah, that works too - no more or less kludgey than the other methods.

Another other reason I wish I could do this. Say I decide I want to
replace a cell w/ a new/improved cell & formula - if I just cut the
new one and paste, it'll error out all the cells dependent on the
destination cell. (This may be more of an issue w/ how cut/paste
works in excel rather than a lack of duplicate capability, but I
imagine ability to clone would fix this too.)
 
A

Arvi Laanemets

Hi

Maybe you give a couple of examples - some functions, and how do you want to
change them. This is a topic, where you can't have any general rules -
mostly every case must have it's own solution.
 
T

Tripp Knightly

Thanks. I can't really offer any specific examples, mainly because I
run into the problem on so many different occasions.

My guess is a *general* solution that offers any automation is going
to be 2 macros, one for "clone copy" and the other for "clone paste".
These macros would do pretty much what you described - copy the
contents of the formula to the clipboard from the edit box, and then
paste those contents into the edit box in your destination cell(s).
The more versatility you want with range operations, the more
complicated the macros get.

Paste-Special offers a whopping 14 different options (in Excel 97, who
knows how many in later versions). What a shame MS couldn't have
allowed an option called "replicate", "duplicate", "clone", "exact",
<insert other name here>.
 
A

Arvi Laanemets

Hi

I asked because at least for 99% cases there is a way to modify the formula
in such a way, that by simple copy-and-paste it will adjust automatically.
You design a formula for a single cell, copy it to range, and you are done.
Copying the formula from toolbar I use, when I want a the same formula on
another worksheet, or I want nearly same formula (with some parameter
changed) for another range (and again - for a single cell only!). These are
really rare ocassions, and so I don't see any need for some macro to do it,
or some considerable gaining in time there.
 
T

Tripp Knightly

Yes am up to speed on abs / relative choice in formulae.

Your extension to right click to enable duplication is *awesome*... thanks.

David McRitchie said:
Hi Tripp,
Assuming that you know about absolute and mixed
references ($A$1, $A1, A$1).

If you want to copy a formula exactly as it is as if
you copied it from the formulabar check out the
*entire* thread:
Chip Pearson via Drew Paterson -- 2001-04-13 misc
http://google.com/groups?threadm=uiqh89AxAHA.1620@tkmsftngp05
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Arvi Laanemets said:
Hi

I asked because at least for 99% cases there is a way to modify the formula
in such a way, that by simple copy-and-paste it will adjust automatically.
You design a formula for a single cell, copy it to range, and you are done.
Copying the formula from toolbar I use, when I want a the same formula on
another worksheet, or I want nearly same formula (with some parameter
changed) for another range (and again - for a single cell only!). These are
really rare ocassions, and so I don't see any need for some macro to do it,
or some considerable gaining in time there.
Click to expand...
Click to expand...
 
Top