Excel VBA - loop over files in folder, copy range, paste in this workbook

Question

I have 500 excel files with data. I would merge all this data into one file.

Task list to achieve this:

1. I want to loop over all the files in a folder
2. open the file,
3. copy this range "B3:I102"
4. paste it into the 1st sheet of the active workbook
5. repeat but paste new data underneath

I've done task 1-4 but i need help with task 5, last bit - pasting the data under the existing data and making it dynamic. I've highlighted this bit with '#### in my code.

Here is my code which I've put together from other people's question :)

Any suggestions on how to do this?

Sub LoopThroughFiles()
Dim MyObj As Object, 
MySource As Object, 
file As Variant
Dim wbThis                  As Workbook     'workbook where the data is to be pasted, aka Master file
Dim wbTarget                As Workbook     'workbook from where the data is to be copied from, aka Overnights file
Dim LastRow As Long
Dim sht1 As Worksheet
Dim sht2 As Worksheet

'set to the current active workbook (the source book, the Master!)
Set wbThis = ActiveWorkbook
Set sht1 = wbThis.Sheets("Sheet1")

Folder = "\\dne\ldc\research-dept\3 CEEMEA\15. EMB\Turkey\TLC Overnight & Weekly Reports\weekly (majeed)\"
Fname = Dir(Folder)

While (Fname <> "")

  Set wbTarget = Workbooks.Open(Filename:=Folder & Fname)
  wbTarget.Activate
  Range("b3:i102").Copy

  wbThis.Activate

  '################################
  'NEED HELP HERE. I GET A ERROR HERE. NEEDS TO BE MORE DYNAMIC.
  sht1.Range("b1:i100").PasteSpecial

 Fname = Dir

 'close the overnight's file
  wbTarget.Close
 Wend

End Sub

Answer 1

I think using variant is useful than copy method.

Sub LoopThroughFiles()

Dim MyObj As Object, MySource As Object

file As Variant
Dim wbThis                  As Workbook     'workbook where the data is to be pasted, aka Master file
Dim wbTarget                As Workbook     'workbook from where the data is to be copied from, aka Overnights file
Dim LastRow As Long
Dim sht1 As Worksheet
Dim sht2 As Worksheet

Dim vDB As Variant

'set to the current active workbook (the source book, the Master!)
Set wbThis = ActiveWorkbook
Set sht1 = wbThis.Sheets("Sheet1")

Folder = "\\dne\ldc\research-dept\3 CEEMEA\15. EMB\Turkey\TLC Overnight & Weekly Reports\weekly (majeed)\"
Fname = Dir(Folder)

While (Fname <> "")

  Set wbTarget = Workbooks.Open(Filename:=Folder & Fname)

  vDB = wbTarget.Sheets(1).Range("b3:i102")

  '################################
  'NEED HELP HERE. I GET A ERROR HERE. NEEDS TO BE MORE DYNAMIC.

        sht1.Range("b" & Rows.Count).End(xlUp)(2).Resize(UBound(vDB, 1), UBound(vDB, 2)) = vDB

 Fname = Dir

 'close the overnight's file
  wbTarget.Close
 Wend

End Sub