Please help with PasteSpecial programming
 

Hi,

I have the following code working fine:

Sub Macro(1)
Selection.Copy
Range("A5").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
End Sub

The problem with this code is the destination cell is hardcoded. I'd
like to be able to select any cell, copy it, select any other cell
(using the mouse to navigate) and PasteSpecial into that cell.

When I manually select a cell, copy and run:

Sub Macro(2)
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
End Sub

by itself, I get the following error message:

Run-time error '1004':

PasteSpecial method of Range class failed.

How do I get Macro(1) to work?

Thanks,

Peter

Please help with PasteSpecial programming
 

Then wouldn't it be easier to do just that using the built in copy button
and the built in paste button. You may have to add the paste button by
customizing your toolbar. It's the one that has a little 12 in the box in
the edit.

--
Don Guillett
SalesAid Software

"Peter" wrote in message
om...
Hi,

I have the following code working fine:

Sub Macro(1)
Selection.Copy
Range("A5").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
End Sub

The problem with this code is the destination cell is hardcoded. I'd
like to be able to select any cell, copy it, select any other cell
(using the mouse to navigate) and PasteSpecial into that cell.

When I manually select a cell, copy and run:

Sub Macro(2)
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
End Sub

by itself, I get the following error message:

Run-time error '1004':

PasteSpecial method of Range class failed.

How do I get Macro(1) to work?

Thanks,

Peter

Please help with PasteSpecial programming
 

How do I get Macro(1) to work?
Do you mean Macro(2)?

Try this (Excel sometimes requires declaring and setting a reference):

Sub Macro(2)
dim rng as Range
set rng = ActiveCell

rng.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:= False, Transpose:=False
End Sub
--
Regards,
Bill


"Peter" wrote in message
om...
Hi,

I have the following code working fine:

Sub Macro(1)
Selection.Copy
Range("A5").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
End Sub

The problem with this code is the destination cell is hardcoded. I'd
like to be able to select any cell, copy it, select any other cell
(using the mouse to navigate) and PasteSpecial into that cell.

When I manually select a cell, copy and run:

Sub Macro(2)
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
End Sub

by itself, I get the following error message:

Run-time error '1004':

PasteSpecial method of Range class failed.

How do I get Macro(1) to work?

Thanks,

Peter

Please help with PasteSpecial programming
 

Thanks for your reply. Yes, I found the Paste Value button. Is there a
way to get that to appear in the pop-up menu when I right-click a
cell?

Peter

"Don Guillett" wrote in message ...
Then wouldn't it be easier to do just that using the built in copy button
and the built in paste button. You may have to add the paste button by
customizing your toolbar. It's the one that has a little 12 in the box in
the edit.

--
Don Guillett
SalesAid Software

"Peter" wrote in message
om...
Hi,

I have the following code working fine:

Sub Macro(1)
Selection.Copy
Range("A5").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
End Sub

The problem with this code is the destination cell is hardcoded. I'd
like to be able to select any cell, copy it, select any other cell
(using the mouse to navigate) and PasteSpecial into that cell.

When I manually select a cell, copy and run:

Sub Macro(2)
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
End Sub

by itself, I get the following error message:

Run-time error '1004':

PasteSpecial method of Range class failed.

How do I get Macro(1) to work?

Thanks,

Peter