Error on code to copy and paste user selection.

**leaning** · 09-16-2011, 10:35 AM

Hello!

I fudged together some code to copy a user-selected range to another user-selected range on the same sheet.

I am getting a "400" error...?

Also, if anyone sees anything else that would make this code less clunky, I would appreciate the help!

Sub CopyandPasteSelection()
Dim CopyFromRange As Range
Dim CopyToRange As Range
Dim lngRowsFrom As Long, lngColsFrom As Long
Dim lngRowsTo As Long, lngColsTo As Long

On Error Resume Next

Application.DisplayAlerts = False
Set CopyFromRange = Application.InputBox(Prompt:= _
                "Please select a range with your mouse to be copied FROM.", _
                    Title:="SPECIFY RANGE", Type:=8)
On Error GoTo 0
Application.DisplayAlerts = True
If CopyFromRange Is Nothing Then
Exit Sub
End If
lngRowsFrom = CopyFromRange.Rows.Count
lngColsFrom = CopyFromRange.Columns.Count

Application.DisplayAlerts = False
Set CopyToRange = Application.InputBox(Prompt:= _
                "Please select a range with your mouse to be copied TO.", _
                    Title:="SPECIFY RANGE", Type:=8)
On Error GoTo 0
Application.DisplayAlerts = True
If CopyToRange Is Nothing Then
Exit Sub
End If
lngRowsTo = CopyToRange.Rows.Count
lngColsTo = CopyToRange.Columns.Count

If lngRowsFrom <> lngRowsTo Then
MsgBox "The number of rows you are copying to is not the same size as the number of rows you are copying from."
Exit Sub
End If

If lngColsFrom <> lngColsTo Then
MsgBox "The number of columns you are copying to is not the same size as the number of columns you are copying from."
Exit Sub
End If


  Range("CopyFromRange").Select
  Selection.Copy
  Range("CopyToRange").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

End Sub

Respecfully,

Lost

**tigeravatar** · 09-16-2011, 11:23 AM

leaning,

Give the following a try:

Sub CopyandPasteSelection()
    
    Dim CopyFromRange As Range
    Dim CopyToRange As Range
    
    On Error Resume Next
    Set CopyFromRange = Application.InputBox(Prompt:= _
                    "Please select a range with your mouse to be copied FROM.", _
                        Title:="SPECIFY RANGE", Type:=8)
    If CopyFromRange Is Nothing Then Exit Sub
    
    Set CopyToRange = Application.InputBox(Prompt:= _
                    "Please select a range with your mouse to be copied TO.", _
                        Title:="SPECIFY RANGE", Type:=8)
    If CopyToRange Is Nothing Then Exit Sub
    
    If CopyFromRange.Rows.Count <> CopyToRange.Rows.Count Then
        MsgBox "The number of rows you are copying to is not the same size as the number of rows you are copying from."
        Exit Sub
    ElseIf CopyFromRange.Columns.Count <> CopyToRange.Columns.Count Then
        MsgBox "The number of columns you are copying to is not the same size as the number of columns you are copying from."
        Exit Sub
    End If
    
    CopyToRange.Value = CopyFromRange.Value

End Sub

Hope that helps,
~tigeravatar

**leaning** · 09-21-2011, 03:58 PM

All,

My other thread sums up how this code and information gets applied.

http://www.excelforum.com/excel-prog...-the-copy.html

HTH!

Lost

**MFO_Jester** · 11-03-2011, 10:52 AM

I don't mean to bring up a dead post but this helped me out tremendously for a schedule spreadsheet we keep at work.

The only thing that I am trying to figure out is how to clear the contents of the original cell (CopyFromRange) at the end of the macro.

Can you help?