macro to find latest created file in folder

[poolf01]

Once a week a dataset will be saved within a specified folder called "Balanced Score Card" on a shared drive. I want a macro that will enable the user to find the latest file by searching for the latest CREATED Date (not modified date) within this folder, open the file and paste the data into an Excel model.

Does anyone know of any code I can use to search for a file with the latest created date?

Many thanks
Faye

 

[clairmont]

I suggest taking the most recently created document, naming it blahblahCURRENT.xls and placing that in the source filename area below.

Hope this helps

Sub Copy()
    Dim PathToFile As String, _
    NameOfFile As String, _
    wbTarget As Workbook, _
    CloseIt As Boolean

' This macro will copy data from another workbook to another.
Application.DisplayAlerts = False
Application.ScreenUpdating = True 'Allows user to see what is happening.
Workbooks.Open Filename:="C:\Documents and Settings\Administrator\My Documents\Source.xls" 'Target source data file
LastRow = Range("C" & Rows.Count).End(xlUp).Row
Range("A1:S" & LastRow).Copy  'copy the latest data (obviously adapt your range)
ActiveWindow.Close 'closes the workbook

Set wbTarget = Workbooks.Open("C:\Documents and Settings\Administrator\My Documents\Recipientdata.xls")  'Recipient data file
Sheets("Metric_Data").Select 'copy the new data in to the signatures sheet
LastRow = Range("C" & Rows.Count).End(xlUp).Row 'copy the latest data (obviously adapt your range)
Range("A1:S" & LastRow).Select
ActiveSheet.Paste

     'If you need to run a macro in that workbook, change !Macro to your Macro's name.
     'Application.Run (wbTarget.Name & "!Macro")

End Sub