OK I managed to fix the loop but it doesn't want to copy the contents o
the cells.

Have a look at this please:


Sub UserForm_Initialize()
Dim i As Integer
Dim SourceWB As Workbook
i = 9

subjnumber = 1 ' change that to the subject number
Application.ScreenUpdating = False
Set SourceWB = Workbooks.Open("I:\Exam results\main.xls"
False, False) ' change that to where the main file is


For z = 2 To ActiveSheet.UsedRange.Rows.Count
thisname = Sheet1.Cells(z, 1)
MsgBox (thisname)

With SourceWB.Sheets(1)
For i = 9 To i + .UsedRange.Rows.Count
If .Cells(i, 3) = thisname Then
ActiveSheet.Cells(z, 9).Value = .Cells(i, 7
subjnumber)
ActiveSheet.Cells(z, 11).Value = .Cells(i, 64
subjnumber)
Exit For
End If
Next i
End With

Next z
SourceWB.Close False
Set SourceWB = Nothing
End Su

--
thewebmaste
-----------------------------------------------------------------------
thewebmaster's Profile: http://www.excelforum.com/member.php...fo&userid=3660
View this thread: http://www.excelforum.com/showthread.php?threadid=56342