PasteSpecial method of Range class failed

[Frank Hayes]

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

[stj...@hotmail.com]

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

[Mike Fogleman]

The code works fine for me. Are you sure the user is doing the 3 steps
properly before running the code?

Mike F
"Frank Hayes" <hay...@verizon.net> wrote in message
news:O1x%23WF95H...@TK2MSFTNGP04.phx.gbl...

[Joel]

Your problem is due to the paste range being inside the copy range. VBA is
not allowing you to select the paste range when one of the cells is already
selected in the copy range. The solution is not to select the source range,
just copy like the code below. Notice I have one line commented out

Sub PasteFormatValues()
'
Range("C10:F10").Copy
' 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

[Frank Hayes]

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

<stj...@hotmail.com> wrote in message
news:1188134279.1...@50g2000hsm.googlegroups.com...

[David Hilberg]

I am having the same problem. A macro I used to use (with XL2000, I
think) is not currently working with XL2003 (11.8146.8132 SP2) on my
WinXP SP2 machine.

Sub PasteValues_SkipBlanks()
' Macro edited 9/2002 by DH
Selection.PasteSpecial Paste:=xlValues, SkipBlanks:=True
End Sub

I tried a reboot, but it still generates the "PasteSpecial method of
range class failed" error. How frustrating!

- David

[Dafreeds]

I am using XL 2003 and I am having a similar problem. I run this
block of code to paste values from one sheet to another. I have used
code like this for years, and all the sudden, this starts breaking.
And, here is the fun part, I run the code and it works sometimes. I
never know when it will fail. It is so random. This is killing me.

I have tried to create objRange object and assign them and it works
sometime and fails others.
Also, I tried adding the line Worksheets("Daily Dashboard").Range
("C72").Select before the first PasteSpecial as to select the cell
first before pasting. Then I get the "Select method of range class
failed"

PLEASE HELP!!! Thanks in Advance!

Here is my code:
Private Sub cmdGetData_Click()

[Result1].Value = ""
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Worksheets("Calculation Sheet").Range("A39:A62").Copy
Worksheets("Daily Dashboard").Range("C72").PasteSpecial
xlPasteValues
Worksheets("Calculation Sheet").Range("C39:C62").Copy
Worksheets("Daily Dashboard").Range("E72").PasteSpecial
xlPasteValues

[Result1].Value = "Complete"
Application.Calculation = xlCalculationAutomatic
Sheets("Control Panel").Select
Application.ScreenUpdating = True

End Sub