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.
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