Paste Identical

[Gromit]

Hi,

I've been trying to develop a VBA procedure that would essentially act
like Paste Special Values, except that the cell contents would be
completely IDENTICAL to the range on the clipboard. This would be very
handy for moving large tables of formulas with mixed absolute and
relative references to somewhere else on the sheet for subsequent
manipulation.

My general approach has been to try to use the Windows clipboard
application to fill a 2D array, turn each array element into a string,
then go the active cell, create a new range the same size as the
clipboard array, paste in the formula strings then Evaluate them.

After several hours I think I'm out of my depth.

Anyone fancy the challenge?

Thanks ever so,

Gromit

 

[Don Guillett]

Post your code efforts.

 

[GB]

I am not quite clear why you wish to do things that way, Gromit? The
problem with copying with the windows clipboard is that all the non-absolute
cell references get translated. However, all I THINK you want to do is copy
the formulae to a new location leaving the original ones unchanged - a bit
like cut and paste but leaving the original cells unchanged.

Maybe you just want something along these lines:

Range("G1:H3").Formula =Range("A1:B3").Formula

Have I misunderstood what you are trying to do?

Incidentally, I am not clear what the best way is of getting the formats of
the cells to copy across. Inheritance does not seem to work in quite the way
I expected it would in VBA. Perhaps someone more knowledgeable can help with
that, please?

Geoff

Post your code efforts.