Copy and paste to another sheet

[JannetteChristie]

I have a button on a sheet with the following code, but am getting the following error message:

Sub CopyDPSSP()

Dim InputRow
Worksheets("DPS-SP").Range("W2:BC2").Copy
InputRow = Application.InputBox(Prompt:="Which Input sheet row should this be copied to ? ", Type:=1)


On Error GoTo 0
If InputRow > 0 Then
Worksheets("Input Sheet").Range("E" & InputRow).PasteSpecial Paste:=xlPasteValues
Else
MsgBox "No row selected"
End If

End Sub

Error message is Run-time error '1004': PasteSpecial method of Range class failed

Can anyone help please?

 

[Fluff]

A few questions, do you
1) have sheet protection on the Input sheet?
2) have merged cells on either sheet?
3) use text to columns?

 

[Joe4]

I was playing around with it, and it didn't like it when I had the InputBox AFTER the Copy command had been activated. Moving it around, it worked when I did this:

Sub CopyDPSSP()

Dim InputRow As Long
InputRow = Application.InputBox(Prompt:="Which Input sheet row should this be copied to ? ", Type:=1)

On Error GoTo 0
If InputRow > 0 Then
    Sheets("DPS-SP").Range("W2:BC2").Copy
    Sheets("Input Sheet").Range("E" & InputRow).PasteSpecial Paste:=xlPasteValues
Else
    MsgBox "No row selected"
End If

End Sub

 

[JannetteChristie]

Perfect, thanks very much.
However after running the script, in the Input Sheet I cannot select just one cell - when I move between cells more than 1 is highlighted.

 

[Joe4]

I don't seem to have that problem. It works fine for me.
However, you can try adding the following line to make sure that you are out of copy mode:

Sub CopyDPSSP()

Dim InputRow As Long
InputRow = Application.InputBox(Prompt:="Which Input sheet row should this be copied to ? ", Type:=1)

On Error GoTo 0
If InputRow > 0 Then
    Sheets("DPS-SP").Range("W2:BC2").Copy
    Sheets("Input Sheet").Range("E" & InputRow).PasteSpecial Paste:=xlPasteValues
[COLOR=#ff0000]    Application.CutCopyMode = False[/COLOR]
Else
    MsgBox "No row selected"
End If

End Sub

 

[JannetteChristie]

Perfect, that seems to have been the problem