2

I have the below set up to copy a list and paste to sheet(data). I want it to display a message when it is successful, telling me which row did it started the paste at. However, the errmsg shows instead.

Thanks in advance

    Dim current As String
   current = ActiveCell.Index
    MsgBox current & "pasted there"

    Exit Sub
errmsg:
    MsgBox "failed to copy."
End Sub

full code

Sub move()
    Range("A3:B3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

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

    Range("F3:I3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft

    Range("F3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft

    Range("A3:G3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy

    On Error GoTo errmsg
    Sheets("data").Select
    Range("A1").Select
    Selection.End(xlDown).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues
    Dim current As String
   current = ActiveCell.Index
    MsgBox current & "pasted there"

    Exit Sub
errmsg:
    MsgBox "failed to copy."
End Sub
Community
  • 1
  • 1
Maki
  • 625
  • 4
  • 11
  • 26
  • 1
    There is no `Index property or method` for range objects. That might be causing the error as what @Pillgram mentioned in his post. You can try `ActiveCell.Row` instead to get the row where you pasted the values. – L42 Mar 09 '15 at 03:53
  • I used .Row instead of .address but both worked. Thanks. – Maki Mar 09 '15 at 04:11

1 Answers1

1

Welcome to SO:) One way to try to debug this would be to remove/comment-out the "On Error GoTo", then run the code. That should show you which line is generating the error. That said, I suspect you want current = ActiveCell.Index to be current = ActiveCell.Address.

Pillgram
  • 804
  • 6
  • 11