Excel VBA – Copy Workbook – Active or External

Excel VBA – Copy Active Workbook

Get syntax for vba function to copy active workbook or external workbook saved in the folder. For both these purpose, we will be using the built in function within workbook object.

Example code: If it is a Worksheet that you want to copy, then there is option .Copy. But in Workbook object there is no direct method to copy. Once you copy the workbook, it can be opened as in this example.

Sub Copy_Active_Workbook()
    Dim NewFilePath As String
    
    'Enter File Path
    NewFilePath = ThisWorkbook.Path & "\Copied_File.xlsm"
    
    'Check if File already exists
    If VBA.Dir(NewFilePath) <> "" Then
        MsgBox "File With Same Name Exists already. Enter Different Name"
        End
    End If
        
    'Create Copy of Active Workbook
    With ActiveWorkbook
        .SaveCopyAs Filename:=NewFilePath
    End With
    
    'This command will also work
    NewFilePath = ThisWorkbook.Path & "\Copied_File1.xlsm"
    ThisWorkbook.SaveCopyAs NewFilePath
    
    'Process Completed
    MsgBox "New file: " & NewFilePath & " is created"
End Sub

Press F5 to execute the above code & check that the file is created in the same path as the Active Workbook.

Excel VBA Open Workbook & Copy To New

Instead of active workbook, if we need to make a copy of another Excel workbook, then use this method. Here, we need to first open the desired file with Workbooks.open command & then use the same command as in previous example.

Here is the example to open any external Excel file & make a copy of it.

Sub Copy_Any_Workbook()
    Dim NewFilePath As String, ExistingFileName As String
    Dim oWB As Workbook
    
    'Enter File Path
    ExistingFileName = ThisWorkbook.Path & "\Original_File.xlsm"
    NewFilePath = ThisWorkbook.Path & "\Copied_File.xlsm"
    
    'Check if File already exists
    If VBA.Dir(NewFilePath) <> "" Then
        MsgBox "File With Same Name Exists already. Enter Different Name"
        End
    End If
    If VBA.Dir(ExistingFileName) = "" Then
        MsgBox "File Does not Exist. Enter Valid File name"
        End
    End If
        
    'Create Copy of Active Workbook
    With Workbooks
        Set oWB = .Open(ExistingFileName)
        oWB.SaveCopyAs NewFilePath
    End With
        
    'Process Completed
    MsgBox "New file: " & NewFilePath & " is created"
End Sub

Make sure to check whether the file to be copied is present in correct path. The error handling for file exist condition is performed with in these 2 sample using the command vba.dir command.

When we pass the filename with path as parameter to this command, if the file does not exist in this path then it will return empty string. If it is present then it will return the filename. Using this we perform the check whether the file exists of not in the given path.