Assuming the user did select the range with cntl-c first, but wanted to put
the paste somewhere of their own choosing, not neccessarily in C13? That is
where the issue is. Thanks for thinking about it.

Frank

wrote in message
ups.com...
On Aug 26, 12:07 pm, "Frank Hayes" wrote:
I am trying to write what I thought would be a very simple macro, but I
am
encountering a "PasteSpecial method of range class failed" runtime error.
The intent of the macro is to take a range that has previously been
selected
by the user and to copy the values and format of the range into a new
cell(s).

I have commented out the original select.copy (as this is the action the
user now takes). Any ideas?

Sub PasteFormatValues()
'
' Range("C10:F10").Select
' Selection.Copy
' Range("C13").Select

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:=
_
False, Transpose:=False
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone,
SkipBlanks:=
_
False, Transpose:=False
Application.CutCopyMode = False

End Sub

Thank you,

Frank Hayes

By commenting out those lines Excel doesn't know where to paste the
data or, if the user has not added something to the clipboard via Ctrl-
C, what to paste. It's not ideal but why not ask the user to select a
range and then run the code as below?

Sub PasteFormatValues()

Selection.Copy
Range("C13").PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= False, Transpose:=False
Range("C13").PasteSpecial Paste:=xlFormats, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
' You may be able to combine the two lines above into the one
below
' Range("C13").PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Application.CutCopyMode = False

End Sub