Excel VBA row copy and pasting error

Question

The following code is copy and pasting rows from sheet1 to sheet2 depending on keyword criteria being met in column E in the first sheet.

Between code lines 5 to 10 I am trying to copy the whole of the tenth row to sheet2 to use as column headers for this sheet, where a pivot will be created.

I am getting "Applicated-defined or Object-defined error" whenever I try to run any operation to copy and paste this row.

Can anyone help?

Sub mileStoneDateChanger()
    Dim r As Long, pasteRowIndex As Long, v() As Long, i As Long
    Dim lastRow As Long
    Dim lCol As Long
    Sheets("Sheet1").Select
    Rows("10:10").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A1").Select
    Selection.Paste
    lastRow = Cells.Find(What:="*",
                    After:=Range("A1"),
                    LookAt:=xlPart,
                    LookIn:=xlFormulas,
                    SearchOrder:=xlByRows,
                    SearchDirection:=xlPrevious,
                    MatchCase:=False).Row
    MsgBox "Last Row: " & lastRow
    pasteRowIndex = 1

    With Sheets("Sheet1")
        For r = 1 To lastRow
            If .Cells(r, "E").Value Like "Milestone*" Then
                If UBound(Split(.Cells(r, "E"), ",")) > 0 Then
                    i = i + 1
                    ReDim v(1 To i)
                    v(i) = pasteRowIndex
                End If
                Sheets("Sheet1").Rows(r).Copy Sheets("Sheet2").Rows(pasteRowIndex)
            pasteRowIndex = pasteRowIndex + 1
            End If
        Next r
    End With

    With Sheets("Sheet2")
        newLastRow = pasteRowIndex
        If IsArray(v) Then
            .Columns(6).Insert shift:=xlToRight
         For i = 1 To newLastRow
                If InStr(1, .Cells(i, "E"), ",") Then
                    .Cells(i, "F").Value = Split(.Cells(i, "E"), ",")(1)
                End If
            Next i
        End If
    End With
End Sub

What syntax do you get with the macro recorder when you perform the same operation manually? That should be a good first check. — QHarr, Nov 20 '17 at 15:54
What sheet are you trying to find last row? This code now finds the last row of Sheet2 — mooseman, Nov 20 '17 at 16:03
@mooseman I find the last row on first sheet and then separately find the last row on the second sheet.. two separate operations — SDROB, Nov 20 '17 at 16:11

score 4 · Accepted Answer · answered Nov 20 '17 at 15:58

4

Here is the copy for row 10 in Sheet1 to A1 in Sheet2

Sheets("Sheet1").Rows("10").Copy Sheets("Sheet2").Range("A1")

answered Nov 20 '17 at 15:58

mooseman

1,997
2
17
29

score 1 · Answer 2 · answered Nov 20 '17 at 15:55

1

Replace Selection.Paste with Selection.PasteSpecial

answered Nov 20 '17 at 15:55

GSD

1,252
1
10
12

Vityata · Answer 3 · 2017-11-20T16:06:21.763

1

Range("A1") cannot be pasted with one whole row. Thus, use the following:

Sub TestME()

    Sheets(1).Select
    Rows("10:10").Select
    Selection.Copy
    Sheets(2).Select
    Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

End Sub

Disclaimer - you really do not want to use Select and Active in VBA. How to avoid using Select in Excel VBA

Best case scenario is probably to avoid Copy like this:

Sub TestME()
    WorkSheets(2).Rows(1) = WorkSheets(1).Rows(10)
End Sub

edited Nov 20 '17 at 16:06

answered Nov 20 '17 at 15:56

Vityata

42,633
8
55
100

I'm getting the same error here on the Range("A1").Select line – SDROB Nov 20 '17 at 16:01
@SDROB - that's strange. Are you sure that you are using **exactly** my code? – Vityata Nov 20 '17 at 16:04

Excel VBA row copy and pasting error

3 Answers3