Excel - xlPasteValues is also pasting Formats

Asked By Nick H on 21-May-10 09:14 AM
This line in a peice of code I have inherited, is pasting formats as
well as values...

Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _

...can anyone suggest why or how I get it to only paste values?

In case its relevant, I have also found that if I add the argument
Transpose:=False then it will fail with a 1004 error.

Br,    Nick

Gary''s Student replied to Nick H on 21-May-10 10:13 AM
I am not seeing that:

Sub Macro1()
Application.CutCopyMode = False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
End Sub

works just fine
Gary''s Student - gsnu201003
Nick H replied to Gary''s Student on 21-May-10 10:46 AM
Thanks Gary,

And so it should. I am fairly confident there is nothing wrong with the
code whatsoever, syntax-wise. However, the fact remains that within my
environment formats are getting pasted when they should not. I need to
hear from someone who knows what might be causing this and how I can
fix it.

Perhaps a bit more background is required since, after a bit more
experimentation, I am wondering if this might be a file type or file
location issue. If I manually copy from an external workbook and
choose PasteSpecial from the ribbon's 'Paste' drop-down I do not see
the usual PasteSpecial dialog but one that offers option buttons of
Paste or PasteLink and a large list of formats.

The workbook containing the code is an xlsb file that gets opened from
a link on a web page. Users enter their data and may even copy and
paste the data to a sheet that is formatted as a UI. Naturally we do not
want them screwing up the interface by pasting formats in from other
workbooks so the 'Paste' action is redirected to our own routine that
contains the PasteSpecial line referred to above.

These efforts are proving impotent though, as formats are getting
pasted anyway!

Br,    Nick
Nick H replied to Nick H on 21-May-10 11:49 AM
It seems there are two distinctly different PasteSpecial operations -
one for pasting from an external session or thread and another for
internal (local?) copy&pasting. I could not figure out a way of
querying the clip-board to find out the source of its contents but, in
case it helps anyone else, I have come up with this 'fudge' that works
for my purposes (beware of line-wrap)...

On Error Resume Next
'This next line will run without error only if copying from an
EXTERNAL thread/session/workbook
ActiveSheet.PasteSpecial Format:="Text", _
Link:=False, _

'This next line will run without error only if copying from an
INTERNAL session
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
On Error GoTo 0