Excel VBA - loop over files in folder, copy range,

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

You can addbelow section as step 5. I have used offset with Variable incremented in loop

Dim i as Long
Range("B1").Select     // 'select the column where you want to paste value
ActiveCell.Offset(i, 0).Select     //'place the offset counter with variable 
sht1.Range("b1:i100").PasteSpecial
i=i+100     //'increment the offset with the number of data rows 

Answer 2

How about you define sht1.Range("b1:i102") as variables instead of constants?

Something like:

Dim x As Long
Dim y As Long
x = 1
y = 1
Dim rng As Range
Set rng = Range("b"&x ,"i"&y)

And then use:

sht1.rng

Just remember to add x = x+100 and y = y +100 at the end of your while statement (so it will update new values between each paste.)

Answer 3

Why don't you place a counter? Like this:

Dim counter As Long
counter = 1

And then:

While (Fname <> "")

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

      wbThis.Activate


      'Solution:

      sht1.Range("b" & counter & ":i" & counter + 99).PasteSpecial
      counter = counter + 100

      Fname = Dir

     'close the overnight's file
     wbTarget.Close
Wend

Answer 4

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

Answer 5

I see you already added a long variable for this, so do a lookup on the last row before you paste. Also, paste in a single cell in case of varying amounts of data.

I altered your script as follows.

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

 'Just add this line:
  lastrow = sht1.Range("b1").End(xlDown).Row + 1
 'And alter this one as follows:
  sht1.Range("B" & lastrow).PasteSpecial

 Fname = Dir

 'close the overnight's file
  wbTarget.Close
 Wend

End Sub