Copy and paste to another sheet

JannetteChristie

Board Regular
Joined
Dec 14, 2015
Messages
117
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?
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,474
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,486
Office Version
  1. 365
Platform
  1. Windows
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:
Code:
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

Board Regular
Joined
Dec 14, 2015
Messages
117
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,486
Office Version
  1. 365
Platform
  1. Windows
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:
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,250
Messages
5,527,633
Members
409,778
Latest member
MagalieD

This Week's Hot Topics

Top