I am atempting to merge data from everyfile in the same directory as my excel file.
I want it to import the data from 28 diferent cells into seperate rows. So far I have:
Sub List()
Dim z As Long, e As Long
Dim f As String
Cells(1, 1) = "=cell(""filename"")"
Cells(1, 2) = "=left(A1,find(""["",A1)-1)"
e = 2
Cells(2, 1).Select
f = Dir(Cells(1, 2) & "*.xls")
Do While Len(f) > 0
ActiveCell.Formula = f
ActiveCell.Offset(1, 0).Select
f = Dir()
Loop
z = Cells(Rows.Count, 1).End(xlUp).Row
For e = 2 To z
MsgBox Cells(e, 1)
Cells(1, 3) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Sheet1'!E76"
Cells(e, 2) = Cells(1, 3)
Next e
MsgBox "collating is complete."
End Sub
Basically, it does the job right. Only that it puts in the first column the name of each file it imports and only imports 1 cell, I want it to import 28 diferent ones that I specify.
What must I add or change?
THank you!!
Bookmarks