Why is the Paste action not working for this range object?

[musicgold]

Hi,

In the following code, I am getting error 1004 on the last line (red). All I am trying to do is copy data from an range and paste it into another range.

Error: Run-time error '1004': Pastespecial method or range class failed.

What am I doing wrong?

Thanks

Public Sub Testing()
Dim Rng1 As Range
Dim oCell As Range
Dim holder As Range
Dim Target As Range
Dim WSaddr As String


Set Rng1 = Selection
Set holder = Range(Rng1, ActiveCell.End(xlDown))

Range(Rng1, ActiveCell.End(xlDown)).Copy


Set Target = Application.InputBox _
         (prompt:="Select Target cell", Title:="Target", Left:=-2, Top:=-10, Type:=8)

   
Target.Select
[COLOR="#FF0000"]Target.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False[/COLOR]

  

End Sub

 

[Fluff]

Try it like

Public Sub Testing()
Dim Rng1 As Range
Dim oCell As Range
Dim holder As Range
Dim Trgt As Range
Dim WSaddr As String


Set Rng1 = Selection
Set holder = Range(Rng1, ActiveCell.End(xlDown))
Set Trgt = Application.InputBox _
         (prompt:="Select Target cell", Title:="Target", Left:=-2, Top:=-10, Type:=8)

Range(Rng1, ActiveCell.End(xlDown)).Copy



   
Trgt.PasteSpecial xlPasteValues

  

End Sub

I would also advise against using VBA keywords such as Target for the names of variables.

 

[musicgold]

Thanks Fluff.

However, I am confused now.

Why is the location of Range(Rng1, ActiveCell.End(xlDown)).Copy in the code making such a difference?

 

[Fluff]

I suspect that when you used the inputbox to get the paste range, it was clearing the clipboard, so there was nothing to paste.

 

[musicgold]

Makes sense. Thank you.

 

[Fluff]

You're welcome & thanks for the feedback