PasteSpecial Method of Range Class Failed Error

[Brian C]

Hi,

I'm running the following code to copy a range (single cell) from "Sheet3"
to a cell in the "Customer Data" sheet. The macro appears to select the
appropriate cell, copies the data, selects the appropriate destination sheet
and cell, then comes back with a Runtime error 1004 Paste Special method of
Range Class Failed error.

I unprotected the sheets, selected only a single cell range, and still get
the error.

Here's the code I'm using. The "Calculations" sheet will be used for other
copy -Past Special steps later in the macro, but can be ignored for now.

Thanks for your help,

Brian

Application.ScreenUpdating = False
destWB.Activate
Sheets("Sheet3").Visible = True
Sheets("Calculations").Visible = True
Sheets("Customer Data").Visible = True
Sheets("Calculations").Unprotect
Sheets("Customer Data").Unprotect


Sheets("Sheet3").Select
Range("A7").Select
Selection.Copy
Application.CutCopyMode = False
Sheets("Customer Data").Select
ActiveSheet.Range("B2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False


***The error occurs while executing the Selection.PastSpecial command***

 

[renegan]

Use

Application.CutCopyMode = False

only after you paste it. In your code it is used before you paste i
which will stop the copy-paste process. So your code cannot fin
anything to paste

 

[Zack Barresse]

Hi Brian,

You can shorten it to a one-liner to transfer values ...

Sheets("Customer Data").Range("B2").Value =
Sheets("Sheet3").Range("A7").Value

HTH

 

[Zack Barresse]

But that does not take away from the pastespecial operation error.

 

[Jim Thomlinson]

As soon as you finish copying the cell you have this line of code

Application.CutCopyMode = False

Thia line cancels the copy so that there is nothing to paste when you get to
your destination...

You can also cleen up some fo the code like this if you wish...

Sheets("Sheet3").Range("A7").Copy
'Application.CutCopyMode = False
Sheets("Customer Data").Range("B2").PasteSpecial Paste:=xlValues
Application.CutCopyMode = False

 

[Brian C]

THANKS GUYS! Got it working and prettied it up using Zack's code.