CODEWITHSUNDEEP
excelvba
Anthony
Anthony

Reputation: 552

Find A Cell In Sheet1 Then Copy The Entire Row That It Is In To The First Empty Row in Sheet2

I receive a "subscript out of range" at the line starting: Sheets(“Sheet1”).Cells("A", i).EntireRow.Copy . How do I copy and paste the row to the first open row in Sheet2.

Sub IDwalkups()
Dim endRow As Long
Dim Match1() As Variant
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
ICount = 0
endRow = Sheet1.Range("B999999").End(xlUp).Row

Match1 = Sheet1.Range("E3:E" & endRow)
For i = LBound(Match1) To UBound(Match1)
    If Match1(i, 1) = "W" Then

    Sheets(“Sheet1”).Cells("A", i).EntireRow.Copy Destination:=Sheets   (“Sheet2”).Range(“A” & Rows.Count).End(xlUp).Offset(1)

    Else
   End If
Next i
End Sub

Upvotes: 0

Views: 330

Answers (2)

YowE3K
YowE3K

Reputation: 23974

The three errors you have are:

  • using “ and ” instead of ". For instance, “Sheet1” is a valid variable name and can be used in statements such as “Sheet1” = 5 * 2. Syntactically, it is quite different to "Sheet1" which is a string literal.
  • Using Cells("A", i) instead of Cells(i, "A") - the first parameter of Cells is the row, and the second parameter is the column.
  • Not qualifying which sheet you are referring to when using Rows.Count (but there is a good chance that this would have worked anyway)

So

Sheets(“Sheet1”).Cells("A", i).EntireRow.Copy Destination:=Sheets   (“Sheet2”).Range(“A” & Rows.Count).End(xlUp).Offset(1)

should have been

Sheets("Sheet1").Cells(i, "A").EntireRow.Copy Destination:=Sheets("Sheet2").Range("A" & Sheets("Sheet2").Rows.Count).End(xlUp).Offset(1)

Upvotes: 1

FunThomas
FunThomas

Reputation: 29296

I see 2 errors. First the Copy & Paste are two steps (2 commands). Second, if you use Cells, you have to give row and column as Number-Parameter. You have to change it to Range.

    If Match1(i, 1) = "W" Then
        Dim sourceRange As Range, destRange As Range
        Set sourceRange = ws.Range("A" & i).EntireRow
        ' or Set sourceRange = ws.Cells(i, 1).EntireRow
        sourceRange.Copy
        Set destRange = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
        destRange.PasteSpecial
    End If

Upvotes: 1

Related Questions