VBA FSO copyfile error

You're not getting objects off the sheet, you're getting text, which is what copyfile is expecting.

Remove the 2 x Set for the file names.


Regards, TMS

Lose the "Me"


Regards, TMS

Maybe the copyfile doesn't like the variant variables. Try Dimming them as String.

You use Set when you are dealing with objects. So, when you say:

Set Filename = Me.Sheets("sheet1").Cells("A1")               'this cell contains the same path as file1 above
Set Sourcename = Me.Sheets("sheet1").Cells("A2")              'this cell contains the same path as file2 above

You are setting the variables to be the range objects.

"Me" in relation to a worksheet would be the workbook.


If you look back at the original code, you were passing copyfile two strings which were fully qualified source and destination locations/filenames. So, you need to pass it two string variables each containing a fully qualified location/filename.

I hope that clarifies and solves the problem.

If not, please post a sample workbook with your code.


Regards, TMS

Oh, and be careful about using what might be Excel/VBA reserved words. Hence, sFileName instead of just FileName, etc

You know, sometimes you can't see the wood for the trees:

Sub sCopyFiles()

' "C:\Users\witham\Google Drive\TESTFOLDER\CS-RAG.pdf", "C:\Users\witham\Google Drive\TESTARCHIVEFOLDER\CS-RAG-june.pdf"

Dim fso
Dim sFileName As String
Dim sSourceName As String

' problem was Cells("A1")
' use
sFileName = Sheets("sheet1").Range("A1")
sSourceName = Sheets("sheet1").Range("A2")
' or
sFileName = Sheets("sheet1").Cells(1, 1)
sSourceName = Sheets("sheet1").Cells(2, 1)

Set fso = CreateObject("scripting.filesystemobject")
fso.copyfile sFileName, sSourceName

End Sub

Regards, TMS

You're welcome. Thanks for the rep.


You have a good one too. We're going to Fountain's Abbey tomorrow so I hope the weather improves!

Regards, TMS