When I manually Copy then Paste SpecialPaste Formats only the formats
are pasted, not the values.
However, when I use the following code in a WorksheetChange Sub...

Cells(1,1).Copy
Target.PasteSpecial Paste:= xlPasteFormats

the value from A1 is also pasted. I want Target to keep it values, not
have them changed to A1's value.
What do I have to do to stop the value from being pasted too?
At the moment I'm using a variant array to store the original values in
Target then feeding those values back into Target after the format has
been pasted.
I am hoping there is an easier way to get the code to work the same way
as the manual process.

Ken Johnson

Hi Ken,

Your code works fine with me ...
Used both ActiveCell and Selection instead of Target, and got all
formats as well as conditional formatting copied ...
HTH
Cheers
Carim

Worked fine for me in xl2000 and xl97.

--
Regards,
Tom Ogilvy



"Ken Johnson" wrote in message
oups.com...
When I manually Copy then Paste SpecialPaste Formats only the formats
are pasted, not the values.
However, when I use the following code in a WorksheetChange Sub...

Cells(1,1).Copy
Target.PasteSpecial Paste:= xlPasteFormats

the value from A1 is also pasted. I want Target to keep it values, not
have them changed to A1's value.
What do I have to do to stop the value from being pasted too?
At the moment I'm using a variant array to store the original values in
Target then feeding those values back into Target after the format has
been pasted.
I am hoping there is an easier way to get the code to work the same way
as the manual process.

Ken Johnson

Thanks Carim and Tom.
I'll have to keep trying.
At the moment it's got me beat.
Ken Johnson

I've got it working now.
I must have just confused myself somehow when testing the code.
Ken Johnson