Looping Issue resolved

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.

VB Code:

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.

Welcome to the Forums.

I formatted your code using the vbcode tags so its easier to read.

Your getting the error on this line - Set wshtTarget = wbTarget.Worksheets("HHT Archive")
Does the sheet exist and is it spelled correctly?

Setp through your code checking the values as you go.

Yes the sheet exsists and it is spelled correctly, I even changed the sheets around to see if it was that sheet or any sheet and it happened to anysheet that was in the fourth loop.

The IITC Offload Master List.xls workbook has how many sheets in it?
Also, the last functions to be performed are destroying the objects. If you
are reusing the smae ones there is no need to do this. It should be on the
outside of the For Next loop.

You may also check during each iteration the sheet count of
"wbTarget.Worksheets.Count", just a hunch.

Thank you for your help, my last arguement was messing me up since it only went to 3 instead of 5.

A second set of eyes always helps. ;)

Ps, dont forget to Resolve your thread ;)