Looping Issue resolved

[elocin_rae]

I'm trying to create a code that will take information from one excel workbook with multiple sheets and put it into another excel workbook on one sheet.
The problem I'm encountering is when the code gets to the 4th loop in setting the wshtarget I get a run-time error type-mismatch. Below is the code which I'm using, anyone that can offer any help it would be much appriciated.

Public Sub Name_Match_Click()
 
   'get the current workbook and sheet
    Set wbSource = ActiveWorkbook
    Set wshtSource = wbSource.Worksheets("QCTotals")
    
     'open a external workbook
    Set wbTarget = Workbooks.Open("C:\WINNT\Profiles\nmayer\Desktop\learn excel\IITC Offload Master List.xls")
 
 For z = 1 To 5
 
 If z = 1 Then
    Set wshtTarget = wbTarget.Worksheets("LTI Archive")
    ElseIf z = 2 Then
     Set wshtTarget = wbTarget.Worksheets("SITCO Archive")
    ElseIf z = 3 Then
    Set wshtTarget = wbTarget.Worksheets("THTI Archive")
    ElseIf z = 4 Then
    Set wshtTarget = wbTarget.Worksheets("HHT Archive")
    ElseIf z = 5 Then
    Set wshtTarget = wbTarget.Worksheets("CNI Archive")
 End If
    'get the required data and do some tricky maths with it
   Rowcount = wshtTarget.UsedRange.Rows.Count
 
 
'defining size of array
ReDim DName(3 To Rowcount)
ReDim Var1(3 To Rowcount)
ReDim SList(3 To Rowcount)
 
 
'_Builds the arrays that contains all Drawing Information
For x = 3 To Rowcount
 
    DName(x) = wshtTarget.Cells(x, 3).Value
    Var1(x) = wshtTarget.Cells(x, 1).Value
    SList(x) = wshtSource.Cells(x, 2).Value
 
Next
 
'set homebook as active workbook here
For x = 3 To Rowcount
 
    For y = 3 To Rowcount
   
        If UCase(DName(x)) = UCase(SList(y)) Then
            wshtSource.Cells(y, 3).Value = Var1(x)
            y = Rowcount
        End If
    Next
  
Next
 
   'close the external workbook and clear up
   If z = 3 Then
    wbTarget.Close
        Set wshtSource = Nothing
        Set wbSource = Nothing
        Set wshtTarget = Nothing
        Set wbTarget = Nothing
    End If
 Next
End Sub

Thanks for your help.