CopyPaste in VBA

Question

I am very new to VBA. I try to copypaste a a worksheet to another worksheet with below code:

 Sub CopyPaste

 Dim Source As String
 Dim Destination As String

 Set Source ="\\D:\folder\source.xls"
 Set Destination="\\D:\folder\destination.xls"
 FileCopy Source, Destination
 End Sub

I mainly use the code provided in: VBA to copy a file from one directory to another

however it gives object required error. I am stuck a this point.

Thanks a lot

You are missing a backslash in between c: and desktop. Also, is your desktop folder in the root directory of your C drive? — Aiden Grossman, Jun 11 '17 at 20:52
I made them up instanteneously. Sorry for inconvenience, I changed them @Aiden Grossman — Jonsi Billups, Jun 11 '17 at 20:57
@JonsiBillups Did you instantiate the fso, like in the linked post? Show your code for FileCopy and specify what line you get the error on. — Absinthe, Jun 11 '17 at 21:09
@Absinthe I get the error in the row where I set Source = "path" which gives me an object required pop up. Other method does not give any error but I see a blank sheet in the destination excel — Jonsi Billups, Jun 11 '17 at 21:25

Cam Ashton · Answer 1 · 2017-06-18T23:46:39.037

1

The two backslashes would be used if you were saving to a network path e.g.

"\\servername\sharename\filename.xls"

The correct syntax for your code should be:

Sub CopyPaste
    Dim Src As String
    Dim Dst As String 

    Src ="D:\folder\source.xls" 
    Dst="D:\folder\destination.xls" 

    FileCopy Src, Dst
End Sub

Also as A.S.H stated remember not to use Set with string variables.

FileCopy has caused me problems in the past , so if you have problems you can try the code below as an alternative:

Sub CopyPaste
    Dim fs As Object
    Dim Src As String 
    Dim Dst As String

    Src = "D:\folder\source.xls"
    Dst = "D:\folder\destination.xls"

    Set fs = CreateObject("Scripting.FileSystemObject")
    fs.CopyFile Src, Dst
    Set fs = Nothing
End Sub

edited Jun 18 '17 at 23:46

answered Jun 11 '17 at 21:46

Cam Ashton

44
4

This is working when I delete Dim Section for Src and Dst – Jonsi Billups Jun 11 '17 at 22:23
Don't use `Set` with Strings. – A.S.H Jun 11 '17 at 23:25
@A.S.H Thanks for the clarification, sorry I wasn't thinking straight last night and I was going from memory as I didn't have a computer with me. – Cam Ashton Jun 12 '17 at 08:00
That explains it :) The downvote is not mine btw. .. it should be retracted now. – A.S.H Jun 12 '17 at 11:45

score 1 · Accepted Answer · answered Jun 11 '17 at 23:14

Set is only used to assign values to object-type variables.

Sub CopyPaste

 Dim Source As String
 Dim Destination As String

 Source ="\\D:\folder\source.xls"
 Destination="\\D:\folder\destination.xls"
 FileCopy Source, Destination
End Sub

Those two backslashes before your paths look odd though.

CopyPaste in VBA

2 Answers2