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.Syntax: Workbook.Savecopyas Filename:=FilenamewithPath
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.Excel VBA - Copy Active WorkbookVisual Basic123456789101112131415161718192021222324Sub Copy_Active_Workbook()Dim NewFilePath As String'Enter File PathNewFilePath = ThisWorkbook.Path & "\Copied_File.xlsm"'Check if File already existsIf VBA.Dir(NewFilePath) <> "" ThenMsgBox "File With Same Name Exists already. Enter Different Name"EndEnd If'Create Copy of Active WorkbookWith ActiveWorkbook.SaveCopyAs Filename:=NewFilePathEnd With'This command will also workNewFilePath = ThisWorkbook.Path & "\Copied_File1.xlsm"ThisWorkbook.SaveCopyAs NewFilePath'Process CompletedMsgBox "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.Syntax: Workbooks.Open (“FilenamewithPath”)
Here is the example to open any external Excel file & make a copy of it.Excel VBA - Copy Any Workbook External PathVisual Basic123456789101112131415161718192021222324252627Sub Copy_Any_Workbook()Dim NewFilePath As String, ExistingFileName As StringDim oWB As Workbook'Enter File PathExistingFileName = ThisWorkbook.Path & "\Original_File.xlsm"NewFilePath = ThisWorkbook.Path & "\Copied_File.xlsm"'Check if File already existsIf VBA.Dir(NewFilePath) <> "" ThenMsgBox "File With Same Name Exists already. Enter Different Name"EndEnd IfIf VBA.Dir(ExistingFileName) = "" ThenMsgBox "File Does not Exist. Enter Valid File name"EndEnd If'Create Copy of Active WorkbookWith WorkbooksSet oWB = .Open(ExistingFileName)oWB.SaveCopyAs NewFilePathEnd With'Process CompletedMsgBox "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.
Excel VBA – Copy Workbook – Active or External