Copy Data From One Spreadsheet To Another

I am trying to copy data from one spreadsheet to another but am having trouble with the VBA code. I am new to doing this so have looked online and tried to find the code to do it. I have come up with the code below but it keeps failing at the While section.

Sub Starters_Click()

Dim Counter As Long
Dim LSearchRow As Integer
Dim LCopyToRow As Integer
Dim Source As Workbook
Dim Dest As Workbook
Dim OriginalWorkBook As Workbook
Set OriginalWorkBook = ThisWorkbook
Const MyDir As String = "C:Documents and SettingsAdministratorDesktop"

On Error GoTo Err_Execute

Application.ScreenUpdating = False

For Counter = 1 To 100

Set Source = Workbooks.Open(MyDir & "HRTest" & Counter & ".xls")..............