Let’s say you’re creating a monthly sales report for your team. Now, you need to show it to your manager, but you also want to create different scenarios, like adjusting sales targets or projecting future earnings. It’s better to make a duplicate or a backup copy of the workbook to avoid accidentally changing the original data.
In Excel, it is easy to make a copy of an existing workbook without following complex steps. In this tutorial, we will learn two easy methods for creating an exact copy of an Excel file.
Copy-Paste an Excel File (Workbook) Make a Copy to Create a Duplicate Copy
This is a generic method for creating a copy of any file in your system. Although it is the quickest way to duplicate a workbook, many Excel users don’t know the method.
- First, select the file for which you want to make a copy.
- After that, use the keyboard shortcut, Ctrl + C to copy that file.
- After that, use the keyboard shortcut Ctrl + V to paste that file to the same location.
- In the end, edit the name of the file and change it to whatever you want to specify.
This is the easiest way to make a copy of an Excel workbook. When you copy and paste a file in the same location, your system creates a copy and adds the word “Copy” after it.
Open as a Copy and Save it to Create a Duplicate Copy
In this method, you open a workbook as a copy. Opening a workbook as a copy means creating a duplicate of the original workbook and working on the duplicate rather than the original.
This is one of the best ways to instantly work on the workbook using a copy. Below are the steps you need to follow:
- First, you need to go to the File tab to go to the backstage view and then click “Open” to open the dialog box to open a workbook.
- After that, locate the file that you want to open as a copy, and instead of clicking on the “Open” button, click on the drop-down and then select the “Open as Copy” option from there.
- At this point, you have a duplicate workbook opened from the original workbook. Now, the most important thing is to save this workbook so that you can work on it. Go to the File tab again and click the “Save As” option.
- In the end, to save the workbook, you can give it a name and choose the location where you want to save it. This could be the same location or anywhere else that meets your requirements.
If the original workbook has links to other files, check these references in the duplicate copy.
Create a Workbook Copy with Drag and Drop
Hold down the Ctrl key on your keyboard. While holding the Ctrl key, click and drag the file to another location in the same folder.
When you release the mouse button, you’ll see that a copy of the file has been created with the same name but with the text “- Copy” at the end of the name, meaning this is a copy of the original workbook.
In the above example, we have the file (text-functions Copy). When I use the drag-and-drop method, I create a new workbook by adding the word (—Copy) at the end of the name. This method is quick and easiest, just like the first one.
VBA code to Create a Duplicate Copy of the Workbook
This code will create a copy of the current workbook in the same folder with the same name and a timestamp in the format yyyy-mm-dd_hh-mm-ss. The timestamp ensures that each copy has a unique name.
First, press Alt + F11 to open the VBA editor and then insert a new module by clicking Insert > Module. Paste the code into the module:
Sub CopyWorkbookWithTimestamp()
Dim currentWorkbook As Workbook
Dim newWorkbookName As String
Dim currentPath As String
Dim timestamp As String
' Get the current workbook
Set currentWorkbook = ThisWorkbook
' Get the current path of the workbook
currentPath = currentWorkbook.Path
' Generate a timestamp
timestamp = Format(Now, "yyyy-mm-dd_hh-mm-ss")
' Create the new workbook name with timestamp
newWorkbookName = currentWorkbook.Name
newWorkbookName = Left(newWorkbookName, InStrRev(newWorkbookName, ".") - 1) & "_" & timestamp & ".xlsm"
' Save a copy of the workbook with the new name
currentWorkbook.SaveCopyAs currentPath & "\" & newWorkbookName
' Notify the user
MsgBox "Workbook copied successfully with the name: " & newWorkbookName
End SubThis method is quick and doesn’t require you to open Excel. It’s like making a copy of a workbook, just like we did in the first method. But in this method, you must create a copy by drag and drop instead of copy-paste.
You can also convert Excel to Google Sheets to create a copy of the workbook. You must upload the Excel file to Google Sheets to do this.
Copy All the Sheets to Create a New Workbook
You can create a duplicate copy of the workbook by copying all the worksheets to a new workbook. When you right-click on any sheet tab at the bottom and choose “Select All Sheets.” It selects all the sheets in the workbook.
And then, right-click again on any of the selected sheets and choose “Move or Copy.” In the dialog box that shows, select “New Workbook” from the dropdown list. And make sure to check the box that says “Create a copy” before clicking “OK.”
When you create a duplicate workbook using this method and the original workbook is a macro enabled, you can’t move the codes that you have in that file. For this, you either need to move copy-paste codes to the new workbook and save that new workbook in “.xlsm” format, or you can use the first method to create an exact copy of the file.