I have a master spreadsheet that is meant to copy values only from multiple source excel sheets in a directory into one sheet. I have the following code which will open and close each file in the directory but the data is only copied from the last file in the directory list. At the moment I am testing this on 2 workbooks. I am using the following code, any help will be most appreciated:

Sub CopyRange()
Application.ScreenUpdating = False
Dim wkbDest As Workbook
Dim wkbSource As Workbook
Set wkbDest = ThisWorkbook
Dim LastRow As Long
Dim iLoop As Long
Dim iMasterLoop As Long

Dim gCount As Long

Const strPath As String = "U:\Raw Data\Pharmacy Data\documents-export-2016-01-14\Test\"
ChDir strPath
strExtension = Dir("*.xlsX*")
Do While strExtension <> ""
Set wkbSource = Workbooks.Open(strPath & strExtension)

Set wkbSource = ActiveWorkbook
Dim sourcedata As Excel.Worksheet
Dim destdata As Excel.Worksheet
Set sourcedata = wkbSource.Worksheets(1)
Set destdata = wkbDest.Worksheets(1)
With wkbSource

gCount = 2

For iLoop = 1 To 65000 Step 1
If sourcedata.Range("A" & iLoop & ":A" & iLoop).Value = "" Then
Exit For
End If
Next iLoop

For iMasterLoop = 2 To (iLoop - 1) Step 1

If sourcedata.Range("A" & iMasterLoop & ":A" & iMasterLoop).Value <> "" Then

destdata.Range("A" & gCount & ":R" & gCount).Value = sourcedata.Range("A" & iMasterLoop & ":R" & iMasterLoop).Value

gCount = gCount + 1

End If
Next iMasterLoop

.Close savechanges:=False

End With
strExtension = Dir
Application.ScreenUpdating = True
End Sub