Hello,
I am very new to VBA in Excel and I am running into an issue. I have conjured up a code that searches a folder and finds the most recent file based on its name (i.e. MRKT_VALUE_20230905 named for 9/5/2023). My code then opens the workbook with that name, but I am unable to figure out how to copy the contents of this workbook, paste it in a current workbook under a sheet named "Market Value", and then close the opened workbook that the contents were copied from.
Any help would be very grateful! Here is what I have so far:
I am very new to VBA in Excel and I am running into an issue. I have conjured up a code that searches a folder and finds the most recent file based on its name (i.e. MRKT_VALUE_20230905 named for 9/5/2023). My code then opens the workbook with that name, but I am unable to figure out how to copy the contents of this workbook, paste it in a current workbook under a sheet named "Market Value", and then close the opened workbook that the contents were copied from.
Any help would be very grateful! Here is what I have so far:
VBA Code:
Sub OpenLatest()
'---Opens a sheet based on date, searches backward from today til it finds a matching date
Dim dtTestDate As Date
Dim sActiveWB As Workbook
Dim sCurrentWB As Excel.Worksheet
Dim sValue As String
Const sPath As String = "J:\LSW_MARKET_INDEX_VALUES\"
Const dtEarliest = #1/1/2016# '--to stop loop if file not found by earliest valid date
Set sCurrentWB = ThisWorkbook.Sheets("Market Values")
dtTestDate = Date
sActiveWB = ActiveWorkbook
While ActiveWorkbook.Name = sActiveWB And dtTestDate >= dtEarliest
On Error Resume Next
Debug.Print "Trying to open: " & _
sPath & "MRKT_VALUE_" & Format(dtTestDate, "YYYYMMDD") & ".xls"
Workbooks.Open sPath & "MRKT_VALUE_" & Format(dtTestDate, "YYYYMMDD") & ".xls"
sValue = sActiveWB.Sheets(1).Range("A1").Value
ThisWorkbook.Sheets(1).Range("B11").Value = sValue
dtTestDate = dtTestDate - 1
On Error GoTo 0
Wend
Application.DisplayAlerts = True
If ActiveWorkbook.Name = sActiveWB Then MsgBox "Earlier file not found."
End Sub