id107
Well-known Member
- Joined
- Apr 16, 2008
- Messages
- 580
Hi. I have a workbook that compiles data that our team has completed each week. They fill in a tracker (A2:I300), send it back and I save them all into one folder amd run a macro to compile the report. Is there any way to also import the name of each file into this report? Preferably into column J.
The code I am currently using is:
The code I am currently using is:
Code:
Sub Compile_Data()
Dim myDir As String, fn As String
myDir = "m:\weekly_reporting\"
fn = Dir(myDir & "*.xls")
Do While fn <> ""
If fn <> ThisWorkbook.Name Then
With Workbooks.Open(myDir & fn)
With .Sheets(1).Range("A2:I300")
ThisWorkbook.Sheets(1).Range("a" & Rows.Count).End(xlUp)(2) _
.Resize(.Rows.Count, .Columns.Count).Value = .Value
End With
.Close False
End With
End If
fn = Dir
Loop
End Sub