Vlookup from various files  
Author Message
avalancheseah





PostPosted: Visual Basic for Applications (VBA), Vlookup from various files Top

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



Microsoft ISV Community Center Forums3