VBA

[Alvaroro84]

I want the macro to pick up the most recent Xl Doc from the My path=F:\VMWare\" location I'm just not sure what else to add to make it pick the most recent

Sub macro2()
'
'
Dim LastRow As Long
Dim MyPath As String
Dim MyFile As String
Dim LatestFile As String
Dim LatestDate As Date
Dim LMD As Date
Dim WB As Workbook
Set WB = ActiveWorkbook
LastRow = Sheets("Sheet1").UsedRange.SpecialCells(xlCellTypeLastCell).Row


MyPath ="F:\VMWare\"



Range("D2").Select
ActiveCell.Formula2R1C1 = _
      "=XLOOKUP(RC[-3],'[" & MyPath & "]Cos'!C3,'[" & MyPath & "]Cos'!C4,0)"


Selection.AutoFill Destination:=Sheets("Sheet1").Range("D2:D" & LastRow), Type:=xlFillDefault


End Sub

 

[Alvaroro84]

In the formula that you originally had (and is at the beginning of the line that is giving the error), there is:

RC[-3]

So that may indicate that the contents in 3 columns to the left of where the formula is being applied (in the active sheet in your Workbook with the VBA) may be wrong.

I've successfully used the formula below but with file name instead so I'm sure that the formula works

ActiveCell.Formula2R1C1 = "=XLOOKUP(RC[-3],'[" & MyPath & LatestFile & "]Cos'!C3,'[" & MyPath & LatestFile & "]Cos'!C4,0)"

 

[cmowla]

I've successfully used the formula below but with file name instead so I'm sure that the formula works

If that's the case, wonderful!

Then that means either the cell C3 and/or C4 in Sheet Cos in the last modified Excel file could be causing the error.

But don't quote me on that.

(Remember, the title of this thread. I provided the code/function you asked for regarding finding the name of the last modified file in the folder. Therefore, I believe it's out of scope of this thread to resolve this issue. If the above is not the cause of the error, and no one else replies to this thread, then start a new one to have someone else help you correct the issue.)

 

[cmowla]

Another cause could be that the active cell is in any of the columns A - C (and not Column D or greater).

 

[Alvaroro84]

If that's the case, wonderful!

Then that means either the cell C3 and/or C4 in Sheet Cos in the last modified Excel file could be causing the error.

But don't quote me on that.

(Remember, the title of this thread. I provided the code/function you asked for regarding finding the name of the last modified file in the folder. Therefore, I believe it's out of scope of this thread to resolve this issue. If the above is not the cause of the error, and no one else replies to this thread, then start a new one to have someone else help you correct the issue.)

Thank you I appreciate the effort once I have a solution I'll make sure to update the post

 

[cmowla]

Wow, I don't know how that happened, but see my post above you. I apparently beat you by a millisecond!