Copy file and paste file with filename stored as variable VBA

[DouglasWicker]

Afternoon All,

I am trying to copy a file from one location to another. Just use FileCopy right?
Well urmm no? The issue I have is that the file that is copied changes and therefore its name address and extension can change. Its full address is saved as a variable. I am trying to paste it in a folder without changing the name of the file and am strugging. Hopefully this code below will help explain what I require.

This works. However it requires me to manually change the name of the file when pasted into the destination folder, therefore is no good. Both the source address is a variable as is the destination folder address.

FileCopy "C:\Test\Test.xlsm", "C:\iamtestinghere\Test.xlsm"

What I need then is this, however it is not working, I get a 75 error, path/ file access error.

FileCopy "C:\Test\Test.xlsm", "C:\iamtestinghere\"

Anyone got any ideas?

 

[Joe4]

Try this:

Sub SpecialCopy()

    Dim myFPName As String
    Dim myNewDir As String
    Dim myFName As String
    
'   Full name and path of original file
    myFPName = "C:\Test\Test.xlsm"
'   Name of new directory to copy to
    myNewDir = "C:\iamtestinghere\"
    
'   Get just file name from original file
    myFName = Mid(myFPName, InStrRev(myFPName, "\") + 1)
    
'   Copy file to new directory with same name
    FileCopy myFPName, myNewDir & myFName
            
End Sub