PasteSpecial Paste:= xlPasteFormats problem

  • Thread starter Thread starter Ken Johnson
  • Start date Start date
K

Ken Johnson

When I manually Copy then Paste Special>Paste 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
 
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
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

PasteSpecial xlPasteFormats ends macro 4
PasteSpecial xlPasteFormats 4
Would like a more efficient way to Copy/PasteSpecial 4
Pasting formats in without conditional formatting? 2
APPLY A CELL's FORMAT WITHOUT SELECTING 3
Cell values to an array, then array transpose to another workbook 8
Conditional Formatting 1
Paste format without borders 3

Back
Top