xlPasteValues is also pasting Formats

  • Thread starter Thread starter Nick H
  • Start date Start date
N

Nick H

This line in a peice of code I've inherited, is pasting formats as
well as values...

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

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

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

Br, Nick
 
I am not seeing that:

Sub Macro1()
Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Range("C2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

works just fine
 
Thanks Gary,

And so it should. I'm 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 shouldn't. 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'm 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 don't 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's formatted as a UI. Naturally we don't
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
 
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 couldn't 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've 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, _
DisplayAsIcon:=False

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