Run time error '9' ,Subscript out of range error

Question

I am new to Excel and VBA programming, I have made attendance sheet and want to copy data from one sheet to other ( month wise) by pressing button.

I am getting error at the following line

lastrow1 = Sheets(“Sheet14”).Range(“A” & Rows.Count).End(xlUp).Row

My code

Sub Button2_Click()

    Dim i As Long, j As Long, lastrow1 As Long, lastrow2 As Long
    Dim myname As String
    lastrow1 = Sheets(“Sheet14”).Range(“A” & Rows.Count).End(xlUp).Row

    For i = 7 To lastrow1
        myname = Sheets(“Sheet14”).Cells(i, “A”).Value

        Sheets(“sheet2”).Activate
        lastrow2 = Sheets(“sheet2”).Range(“A” & Rows.Count).End(xlUp).Row

        For j = 7 To lastrow2

            If Sheets(“sheet2”).Cells(j, “A”).Value = myname Then
                Sheets(“Sheet14”).Activate
                Sheets(“Sheet14”).Range(Cells(i, “D”), Cells(i, “AH”)).Copy
                Sheets(“sheet2”).Activate
                Sheets(“sheet2”).Range(Cells(j, “D”), Cells(j, “AH”)).Select
                ActiveSheet.Paste
            End If

        Next j
        Application.CutCopyMode = False
    Next i
    Sheets(“Sheet14”).Activate
    Sheets(“Sheet14”).Range(“D7”).Select
End Sub

lastrow2 = Sheets("sheet2").Range("A" & Sheets("sheet2").Rows.Count).End(xlUp).Row — Kanan

Shai Rado Shai Rado · Accepted Answer · 2017-01-16T06:11:18

Your code had the wrong type of “ , instead of ".

It's better to stay away from Activate , Select and ActiveSheet and use referenced Worksheet and Ranges instead (it will also be faster).

Try the code below, it will do the same, just a little faster (and more reliable not depending on the ActiveSheet.

Modified Code

Sub Button2_Click()

Dim i As Long, j As Long, lastrow1 As Long, lastrow2 As Long
Dim myname As String

With Sheets("Sheet14")
    lastrow1 = .Range("A" & .Rows.Count).End(xlUp).Row

    ' take the line below outside the For loop, there is no need to get the last row evey time 
    lastrow2 = Sheets("sheet2").Range("A" & Sheets("sheet2").Rows.Count).End(xlUp).Row        
    For i = 7 To lastrow1
        myname = .Range("A" & i).Value

        For j = 7 To lastrow2
            If Sheets("sheet2").Range("A" & j).Value = myname Then
                .Range("D" & i & ":AH" & i).Copy Destination:=Sheets("sheet2").Range(Range("D" & j & ":AH" & j))
            End If
        Next j
        Application.CutCopyMode = False
    Next i
End With

End Sub

Note: if the data in sheet2 and sheet14 are unique (appear only once in the entire sheet) consider using the Match function, it will save you 1 For loop.

Run time error '9' ,Subscript out of range error

1 Answers