Open Most Recent Document in a Folder AND Have Dynamic Vlookups

[bdexceluser]

I am working to create a document that using VBA code can open the most recent document in a folder each month when the report is run and then do vlookups from that document. I have a code that opens the most recent document in a file folder location but when I record the code for the vlookups, it references a specific document (the date in the title of the document changes monthly).

Is there a way to modify the vlookup code so that the lookups will occur off whatever the most recent document is and not a fixed document reference?

Code below:

Sub Step7_Open_File()
'
' Step7_Open_File Macro
'
'
'Declare the variables
Dim MyPath As String
Dim MyFile As String
Dim LatestFile As String
Dim LatestDate As Date
Dim LMD As Date

'Specify the path to the folder
MyPath = "C:\Users\USERNAME\FOLDERLOCATION"

'Make sure that the path ends in a backslash
If Right(MyPath, 1) <> "" Then MyPath = MyPath & ""

'Get the first Excel file from the folder
MyFile = Dir(MyPath & "*.*", vbNormal)

'If no files were found, exit the sub
If Len(MyFile) = 0 Then
MsgBox "No files were found...", vbExclamation
Exit Sub
End If

'Loop through each Excel file in the folder
Do While Len(MyFile) > 0

'Assign the date/time of the current file to a variable
LMD = FileDateTime(MyPath & MyFile)

'If the date/time of the current file is greater than the latest
'recorded date, assign its filename and date/time to variables
If LMD > LatestDate Then
LatestFile = MyFile
LatestDate = LMD
End If

'Get the next Excel file from the folder
MyFile = Dir

Loop

'Open the latest file
Workbooks.Open MyPath & LatestFile

End Sub

-----------------------------------------------------------------------------------------------

Then this is the code to do lookups off the file that was just opened, as you can see the Vlookups reference the 6.1.2018 file. What I would like to do is modify the lookups so they run with whatever file is opened up. When the report is run 6.30.2018, I would like for the macro to automatically open the file (VBA Code above and that works) and then have dynamic Vlookup references so they would go into the newly opened 6.30 document without me needing to modify the code:



Sub Step8_Demand_Spread_Lookups()
'
' Step8_Demand_Spread_Lookups Macro
'
'
Windows("Buy Template.xlsm").Activate
Range("A1").Select
ActiveCell.Offset(1, 14).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-6],'[Retail 6.1.2018.xlsx]Tab 1 Pivot'!C1,1,0)"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-7],'[Retail 6.1.2018.xlsx]Tab 2 Pivot'!C1,1,0)"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-8],'[Retail 6.1.2018.xlsx]Tab 3'!C1,1,0)"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-9],'[Retail 6.1.2018.xlsx]Tab 4'!C1,1,0)"
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlToLeft).Select

Range("A1").Select
ActiveCell.Offset(0, 13).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1:D1").Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown
Selection.End(xlUp).Select
Selection.End(xlToLeft).Select

Range("A1").Select
ActiveCell.Offset(1, 14).Range("A1:D1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.End(xlUp).Select
Selection.End(xlToLeft).Select
Application.CutCopyMode = False
Windows("Buy Template.xlsm").Activate
End Sub



Any help would be much appreciated!

Thank you!

Bdexceluser

 

[JazzSP8]

Hi - Welcome to the Forum

Try amending your Formulas to something like this;

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-6],'[" & LatestFile & "]Tab 1 Pivot'!C1,1,0)"

 

[bdexceluser]

Hey JazzSP8,

Thanks so much for the quick response!

For the code above, how do I direct it to a specific file folder location so it knows where to look for the most recent file?

Thanks again for the help!

Bdexceluser

 

[JazzSP8]

The easiest thing would to be make it all one step if you can, that way you just need to find the 'LatestFile' just the once.

(There may be better ways of doing this that you could find, but to keep it familiar to you with your own code...)

Sub Step7_Open_File()
'
' Step7_Open_File Macro
'
'
'Declare the variables
Dim MyPath As String
Dim MyFile As String
Dim LatestFile As String
Dim LatestDate As Date
Dim LMD As Date

'Specify the path to the folder
MyPath = "C:\Users\USERNAME\FOLDERLOCATION"

'Make sure that the path ends in a backslash
If Right(MyPath, 1) <> "" Then MyPath = MyPath & ""

'Get the first Excel file from the folder
MyFile = Dir(MyPath & "*.*", vbNormal)

'If no files were found, exit the sub
If Len(MyFile) = 0 Then
    MsgBox "No files were found...", vbExclamation
    Exit Sub
End If

'Loop through each Excel file in the folder
Do While Len(MyFile) > 0

'Assign the date/time of the current file to a variable
LMD = FileDateTime(MyPath & MyFile)

'If the date/time of the current file is greater than the latest
'recorded date, assign its filename and date/time to variables
If LMD > LatestDate Then
    LatestFile = MyFile
    LatestDate = LMD
End If

'Get the next Excel file from the folder
MyFile = Dir

Loop

'Open the latest file
Workbooks.Open MyPath & LatestFile

Windows("Buy Template.xlsm").Activate
Range("A1").Select
ActiveCell.Offset(1, 14).Range("A1").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-6],'[" & LatestFile & "]Tab 1 Pivot'!C1,1,0)"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-7],'[" & LatestFile & "]Tab 2 Pivot'!C1,1,0)"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-8],'[" & LatestFile & "]Tab 3'!C1,1,0)"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-9],'[" & LatestFile & "]Tab 4'!C1,1,0)"
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlToLeft).Select

Range("A1").Select
ActiveCell.Offset(0, 13).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1:D1").Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown
Selection.End(xlUp).Select
Selection.End(xlToLeft).Select

Range("A1").Select
ActiveCell.Offset(1, 14).Range("A1:D1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Selection.End(xlUp).Select
Selection.End(xlToLeft).Select
Application.CutCopyMode = False
Windows("Buy Template.xlsm").Activate

End Sub

 

[bdexceluser]

Hey JazzSp8,

I finally tried the code and it worked like a charm!

You rock! Thank you so much!

 

[bdexceluser]

Hey JazzSP8,

I have two more questions if you have time:

How can I have the macro perform a search or filter and if the selection I am looking for is unavailable, to end the macro or go to the next step?

Also, if I wanted to perform the lookups and then close out of the document at the very end of the macro, what code should I tack on to the end?

Thanks so much for all your help!

bdexceluser