Public Function funcLastRow(shtTarget As Worksheet, Optional iColLimit As Integer = -1) As Long

    If iColLimit = -1 Then
        iColLimit = 256
    End If

    Dim rowMaxIndex As Long
    rowMaxIndex = 0

    Dim ctrCols As Integer
    For ctrCols = 1 To iColLimit
    If shtTarget.Cells(1048576, ctrCols).End(xlUp).Row > rowMaxIndex Then
            rowMaxIndex = shtTarget.Cells(1048576, ctrCols).End(xlUp).Row
        End If
    Next ctrCols

    funcLastRow = rowMaxIndex

End Function

Dim lLastRow As Long
lLastRow = funcLastRow(Sheets(1))

A2 = INDEX(Sheet1!A:A,1+QUOTIENT(ROW()-ROW($A$2)+6,6))

B2 = VLOOKUP($A2,Sheet1!$A$2:$I$3000,COLUMNS($A$1:B$1),FALSE)

C2 = VLOOKUP($A2,Sheet1!$A$2:$I$3000,COLUMNS($A$1:C$1),FALSE)

D2 = VLOOKUP($A2,Sheet1!$A$2:$I$3000,COLUMNS($A$1:D$1),FALSE)

G2 = "" & IF(SUMPRODUCT(--(Sheet1!$A$1:$I$1=F2))>0,INDEX(Sheet1!$I$1:$I$3000,MATCH(A2,Sheet1!$A$1:$A$3000,0)),INDEX(Sheet2!$G$1:$G$3000,MATCH(A2,Sheet1!$A$1:$A$3000,0)))