VBA Copy Workbook in Excel

VBA Copy Workbook is to copy workbook or file using FSO.CopyFile method. Where FSO is nothing but FileSystemObject object. It copies a file from one location to another. We can also create user defined function to copy file.

Syntax for Copy Workbook Method

Here is the following syntax to Copy Workbook.

Object.CopyFile(Source As String, Destination As String, [OverWriteFiles As Boolean=True])

Object is a required argument. It always represents FileSystemObject object.
Source is a required argument. It specifies file path to be copied. It represents string data type. The source file path can be relative or absolute. Wildcard characters can be included.
Destination is a required argument. It specifies file path to create. It represents string data type. Wildcard characters can’t be included.
OverWriteFiles is an optional argument. It specifies whether file to be overwritten or not. It’s default value is TRUE.

Macro to Copy Workbook using FSO.CopyFile Method

Let us see the following example macro to Copy Workbook using FSO.CopyFile Method.It copies Workbook form one location to another location.

'VBA Copy Workbook using FSO.CopyFile in Excel
Sub VBA_Copy_Workbook_FSO()
    'Variable declaration
    Dim oFSO As FileSystemObject
    Dim sFilePath As String
    Dim dFilePath As String
    'Source & Destination File Path
    sFilePath = "D:\VBAF1\VBA Functionsa.xlsm"
    dFilePath = "D:\Blog\VBA Functionsa.xlsm"
    'Reference Workbook by Object named oWorkbook
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    'Copy Workbook
    oFSO.CopyFile sFilePath, dFilePath, True
End Sub

Note: If you get any error while running above code, add below specified reference.
VBE->Tools->References->Check the box Microsoft Scripting Runtine

Instructions to use Macro

Here are the instructions to use above macro procedure in Visual basic editor.

  • Copy Visual Basic Editor(VBE) by clicking Alt +F11
  • Go to code window by clicking F7
  • Copy above specified macro or procedure
  • Paste above copied code in code window
  • Run macro by clicking F5 or Run command
  • You can see output on the screen
  • Find above output screenshot of the specified procedure.

Related Workbook Object articles

You may also like the related Workbook Object articles.

VBA Workbook Object

Leave a Comment

Your email address will not be published. Required fields are marked *