PasteSpecial Method of Range Class Failed Error
 

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***

PasteSpecial Method of Range Class Failed Error
 

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

--
renega
-----------------------------------------------------------------------
renegan's Profile: http://www.excelforum.com/member.php...fo&userid=1045
View this thread: http://www.excelforum.com/showthread.php?threadid=52816

PasteSpecial Method of Range Class Failed Error
 

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

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM


"Brian C" wrote in message
...
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***

PasteSpecial Method of Range Class Failed Error
 

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

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM


"renegan" wrote in
message ...

Use

Application.CutCopyMode = False

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


--
renegan
------------------------------------------------------------------------
renegan's Profile:
http://www.excelforum.com/member.php...o&userid=10450
View this thread: http://www.excelforum.com/showthread...hreadid=528168

PasteSpecial Method of Range Class Failed Error
 

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

--
HTH...

Jim Thomlinson


"Brian C" wrote:

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***

PasteSpecial Method of Range Class Failed Error
 

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

"Zack Barresse" wrote:

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

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM


"Brian C" wrote in message
...
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***