Edit looping code to copy range from one workbook to another

Heya!

Private Sub test()
Dim x As Workbook, y As Workbook
Dim sdt As String, wbNam As String, dt As String, ldt As String, i As Long

wbNam = "Productivity "
dt = Sheet1.Range("B1").Value
sdt = Format(CStr(dt), "m.d.yy") & ".xlsx"
ldt = Format(CStr(dt), "yyyy") & "\" & Format(CStr(dt), "mm") & "_" & MonthName(Month(dt)) & "_" & Year(dt)

With Sheets("Team One")
    sn = Cells(1).CurrentRegion.Value
End With

Set y = Workbooks.Open("S:\" & ldt & "\" & wbNam & sdt)

For i = 2 To UBound(sn)
    If sn(i, 1) <> vbNullString Then
Set x = ThisWorkbook

'Now, copy what you want from x:
        x.Activate
        ActiveSheet.Cells.Find(sn(i, 2)).Resize(10, 8).Offset(1, 0).Copy y.Sheets("MSP WPS").Cells.Find(sn(i, 2)).Offset(1, 0)
        ActiveSheet.Cells.Find(sn(i, 2)).Resize(2, 8).Offset(12, 0).Copy y.Sheets("MSP WPS").Cells.Find(sn(i, 2)).Offset(12, 0)

'Close x
        x.Close SaveChanges:=True
    End If

Next

End Sub

So I had a thread on this yesterday and I didn't want to dredge it up again, so I'm posting a new thread with the code I've compiled thus far.

Attached is an example workbook. Sheet1 and Sheet2 on this workbook would be separate workbooks in practice, but are just sheets for consolidation purposes.

Basically, what I'm trying to do is have Excel use the value in Sheet1, A2 as the lookup option, copy the specified range, then find the value on Sheet 2 and paste it. Then I'd like it to move on to the next name on Sheet 1 which is in A18, and repeat the exact same process. Then do that over and over until it hits a blank, then stop.

The concept of looping makes sense, but the declaration of i or x as Long is wildly confusing to me, so I'm not sure how to tell the code to execute what I have above starting at A2 then A18, then A34, etc etc.

I spent a majority of yesterday evening trying to google it, but this is the best I've come up with so far and it isn't working. If anyone could identify the flaw in my code/logic and help me out, I'd appreciate it!