Master file to add columns based on all excel files in a single folder

[rockdrigotoca]

Hi guys!!

I have been trying to find a code for this, tried some of them but still cannot find what I need. I hope you might be able to help?

I created an allocation spreadsheet that sends an email to each person with their allocated work for the day. This is in c:\\Allocation\Template.xls

After clicking a button, it sends each person their unique file but also copies a full file in c:\\Allocation\Historicals\ saved as "Allocation" & date format with hour and seconds.

In a week, I would like to be able to see what have I assigned to each person. The difficult bit is that each file has something like:

Columns: B C D E F G H
Row3: Task Initials # Initials # Initials #
Row4: Task1 RT 10
Row5: Task2 EM 20 RT 20

Is there a way to gather all these numbers from each workbook under \Historicals\ and put them in a Master file so that it is easy to see a summary?

For example, I will be able to know that on the 4th of feb (date on B2 from each file), I assigned task 1 to RT and on the 5th I assigned it to EM so that will enable me not to assign on the 6th the same task to the same person??

Can anyone help me please??

 

[Trevor G]

Here is code to open all workbooks in a folder, if you record a macro to open 1 workbook and then copy the data and then clse the workbook it will give most of the code you need and then adjust this code to include copying and pasting into a master file,

Excel to open files in a folder Excel 2007
Sub x()
Dim strFilename As String
Dim strPath As String
Dim wbkTemp As Workbook

strPath = "C:\Access VBA Practice\"
strFilename = Dir(strPath & Range("a1").Value & "*.xls")
Do While Len(strFilename) > 0
Set wbkTemp = Workbooks.Open(strPath & strFilename)
'
' do your code with the workbook
'

' save and close it
'wbkTemp.Close True

strFilename = Dir
Loop

End Sub