Macro to copy data from multiples files

p1rate78,

This works in my test environment.

The macro will not open the files. It will create formulae in your workbook, and then change the formulae to values.

And, it will copy the filename into column B, as a reference.


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Press and hold down the 'ALT' key, and press the 'F11' key.

On the 'Insert' menu, click 'Module'.

Copy the below code, and paste it into the Module (on the right pane).

Option Explicit
Sub GetData()
Dim MyDir As String, FN As String, SN As String
myDir = "E:\data\test folder"
sn = "Sheet3"
Application.ScreenUpdating = False
FN = Dir(MyDir & "\08*.xls")
Do While FN <> ""
  If FN <> ThisWorkbook.Name Then
    With ThisWorkbook.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp)(2)
      .Formula = "=SUM('" & MyDir & "\[" & FN & "]" & SN & "'!F7:F26)"
      .Value = .Value
      .Offset(, 1).Value = FN
    End With
  End If
FN = Dir
Loop
Application.ScreenUpdating = True
End Sub

Then run the "GetData" macro.


Have a great day,
Stan