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.