Rick Rothstein expressed precisely :
So then, the line could be written something like:

rngSource.Copy rngTarget

..which could be anywhere on any sheet in any open workbook.

Okay, there are two problems with doing that. First, if rngSource is
non-contiguous, rngTarget cannot be non-contiguous... so neither of our
methods could be used for the OP's request if his initial range is
non-contiguous. But, second, the bigger problem with your approach (at least
with respect to the OP's question) is that if rngSource is non-contiguous,
what gets copied to rngTarget is the **values** in rngSource, **not** the
formulas in those cells... the formulas are copied with the Copy method only
when the cells being copied are contiguous.

Rick Rothstein (MVP - Excel)

I agree. In the context of this OP's use, your solution is (IMO) better
than mine. My point was about the flexibility of the solution in other
contexts.

Your solution requires that the source be contiguous cells AND the
destination be identical as well as contiguous.

My solution requires only the source be contiguous. The destination
could be a single cell (allbeit that adjacent cells will be
overwritten).

If the source cells are not contiguous then we would be dealing with
areas OR an array, which <IMO needs to be handled differently.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc