Vlookup from various files
Hi,
I have the below macro that pull data from different files based on the dates stated in Column A. This assumes that the data can always be found from cell B2.
However, I will like to have a macro that will do a vlookup from the open files. Pls advise.
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 15/08/2006 by User
Dim filedate As String
Dim times As Integer
Dim filefound As String
Dim thisfile As String
For times = 1 To ActiveSheet.UsedRange.Rows.Count
filedate = Range("A" & times).Value
thisfile = "C:\Documents and Settings\Simon\Desktop\VBA\VBE\" & filedate & ".xls"
filefound = Dir(thisfile)
If filefound <> "" Then
Workbooks.Open Filename:= _
"C:\Documents and Settings\Simon\Desktop\VBA\VBE\" & filedate & ".xls"
Range("B2").Select
Selection.Copy
ActiveWindow.ActivateNext
Range("B" & times).Select
ActiveSheet.Paste
ActiveWindow.ActivateNext
ActiveWindow.Close
Range("A1").Select
Else
Range("B" & times).Value = "C:\Documents and Settings\Simon\Desktop\VBA\VBE\" & filedate & ".xls"
End If
Next times
End Sub
Rgds,
Simon
