Please help, I know this is easy I'm just missing something. I want to take the last created excel file in a directory and copy from A2:L300 and paste that in the xls that will house this vba code in the respective cells A2:L300. This is what I have so far. The files can be in the same folder if needed.
Code:
Sub Workbook_open()
Dim FileSys As Object
Dim objFile As Object
Dim myFolder
Dim strFilename As String
Dim dteFile As Date
Dim FileName As String
Dim path As String
Dim currentWb As Workbook
Dim openWb As Workbook
Dim openWs As Worksheet
'set path for files - change for your folder
'Set const on next line to your folder path
Const myDir As String = "C:\Users\BTATE\Downloads"
'set up filesys objects
Set FileSys = CreateObject("Scripting.FileSystemObject")
Set myFolder = FileSys.GetFolder(myDir)
'loop through each file and get date last modified. If largest date then store Filename
dteFile = DateSerial(1900, 1, 1)
For Each objFile In myFolder.Files
'Debug.Print objFile.Name
If InStr(1, objFile.Name, ".xls") > 0 Then
If objFile.DateLastModified > dteFile Then
dteFile = objFile.DateLastModified
strFilename = objFile.Name
End If
End If
Next objFile
' Workbooks.Open myDir & Application.PathSeparator & strFilename
FileName = myDir & Application.PathSeparator & strFilename
'MsgBox (FileName)
Set FileSys = Nothing
Set myFolder = Nothing
End Sub
