• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Change code to copy file instead of move file

Callie

New Member
Hello,

I have code that moves the newest file (based on saved date) in a specified folder to a specified folder on another directory. I would like to copy the file to the destination folder, rather than move it. I need the file to remain in the original location.

Thanks in advance for suggestions.

Code:
Sub Open_Latest_File_Copy_Move()
Dim strPath As String
Dim strDest As String
Dim myFile As String
Dim LatestFile As String
Dim LatestDate As Date
Dim Lmd As Date
Dim Wb As Workbook
Dim fso As Object
'This Folder Contains The File To Be Checked
strPath = "V:\2018\Source\"
'This Folder is the Destination
strDest = "I:\2018\Target\"
Set fso = VBA.CreateObject("Scripting.FileSystemObject")
myFile = Dir(strPath & "*.xls*", vbNormal)
If Len(myFile) = 0 Then
MsgBox "No Files Were Found...", vbExclamation
Exit Sub
End If
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Do While Len(myFile) > 0
Lmd = FileDateTime(strPath & myFile)
If Lmd > LatestDate Then
LatestFile = myFile
LatestDate = Lmd
End If
myFile = Dir
Loop
Set Wb = Workbooks.Open(strPath & LatestFile)
Wb.Sheets("Sheet1").UsedRange.Copy ThisWorkbook.Sheets("Sheet1").Range("A1")
Wb.Close SaveChanges:=False
strPath = strPath & LatestFile
Call fso.CopyFile(strPath, strDest)
Kill strPath
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Set Wb = Nothing
MsgBox "Done...", 64
End Sub
 
Back
Top