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.

[RobDog888]

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.

[elocin_rae]

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.

[RobDog888]

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.

[elocin_rae]

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

[RobDog888]

A second set of eyes always helps. ;)

Ps, dont forget to Resolve your thread ;)