Home How to loop through a folder of workbooks, copy and paste
 Please advise how to loop through a folder for the latest workbooks as there's always an update file, copy and paste the file data? Hey guys, sorry for missing the code in the earlier question: Force the explicit delcaration of variables Option Explicit Sub OpenLatestFile() 'Declare the variables Dim MyPath As String Dim MyFile As String Dim LatestFile As String Dim LatestDate As Date Dim LMD As Date 'Specify the path to the folder MyPath = "\\SNG-FS-01\PacificLight Power (City)\Working Files\Consolidate File\2017\" 'Make sure that the path ends in a backslash If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\" 'Get the first Excel file from the folder MyFile = Dir(MyPath & "*.xls", vbNormal) 'If no files were found, exit the sub If Len(MyFile) = 0 Then MsgBox "No files were found...", vbExclamation Exit Sub End If 'Loop through each Excel file in the folder Do While Len(MyFile) > 0 'Assign the date/time of the current file to a variable LMD = FileDateTime(MyPath & MyFile) 'If the date/time of the current file is greater than the latest 'recorded date, assign its filename and date/time to variables If LMD > LatestDate Then LatestFile = MyFile LatestDate = LMD End If 'Get the next Excel file from the folder MyFile = Dir Loop 'Open the latest file Workbooks.Open MyPath & LatestFile  End Sub With this code able to open the latest file, but have no idea how to copy the open file data (file name is subject to change) and paste it into the MyFile Appreciate if anyone of you could help on this