See, once you post up some useful data, it goes quickly. Put this macro into your Master Database program, run it from there. You will need to edit the part I marked in red to the folder you will use each time.
This macro will run on ALL the files in the folder starting with the "Agent Call Report...." in the name.
Sub SuperVisorFillIn()
Dim i As Integer, wb As Workbook, LR As Long
Range("A2", Range("B2").End(xlDown)).Sort Key1:=Range("A2"), _
Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
ActiveWorkbook.Names.Add Name:="Super", RefersToR1C1:="=Sheet1!R2C1:R" & Range("B" & Rows.Count).End(xlUp).Row & "C2"
With Application.FileSearch
.NewSearch
.LookIn = "C:\Documents and Settings\All Users\Documents\My Documents\Excel Tips\Test"
.SearchSubFolders = False
.Filename = "Agent Call Report*.xlsx"
.Execute
For i = 1 To .FoundFiles.Count
'Open each workbook
Set wb = Workbooks.Open(Filename:=.FoundFiles(i))
'Perform the operation on the open workbook
Range("G1") = "Supervisor"
Columns("G:G").Columns.AutoFit
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("G2:G" & LR).FormulaR1C1 = "=VLOOKUP(RC1,'Master Database.xls'!Super,2,FALSE)"
Columns("G:G").Value = Columns("G:G").Value
'Save and close the workbook
wb.Save
wb.Close
Next i
End With
End Sub
The macro also sorts the Master Database info and gives it a named range.
Bookmarks