Excel Champs / Excel Basics / Make a Copy of the Excel Workbook (Duplicate File)

Make a Copy of the Excel Workbook (Duplicate File)

80+ Excel Keyboard Shortcuts ➜

✍️ Written by Puneet Gogia (Microsoft MVP)

Table of Content hide
Copy-Paste an Excel File (Workbook): Make a Copy to Create a Duplicate Copy
Open as a Copy and Save it to Create a Duplicate Copy
Create a Workbook Copy with Drag and Drop
VBA code to Create a Duplicate Copy of the Workbook
Copy All the Sheets to Create a New Workbook
Related Tutorials

Methods to Duplicate an Entire Workbook

Method Type
Steps
Save As
File ➜ Save As ➜ Choose location ➜ Enter new name ➜ Save
Save a Copy
File ➜ Save a Copy ➜ Choose location ➜ Save (for OneDrive/cloud files)
Open as Copy
File ➜ Open ➜ Browse ➜ Select file ➜ Open dropdown arrow ➜ Open as Copy
File Explorer – Copy/Paste
Select file ➜ Ctrl+C ➜ Ctrl+V (creates “filename – Copy”)
File Explorer – Right-click
Right-click file ➜ Copy ➜ Right-click ➜ Paste
File Explorer – Ctrl+Drag
Hold Ctrl ➜ Drag file within same folder ➜ Release
OneDrive / SharePoint Online
Open OneDrive in browser ➜ Right-click file ➜ Copy to / Download a copy
Save as Template
File ➜ Save As ➜ Choose Excel Template (.xltx) ➜ Save ➜ Create new workbook from it
VBA – SaveCopyAs
Alt+F11 ➜ Insert module ➜ ActiveWorkbook.SaveCopyAs "C:\path\Copy.xlsx" ➜ Run
VBA – FileCopy
Alt+F11 ➜ Insert module ➜ FileCopy "C:\source.xlsx", "C:\copy.xlsx" ➜ Run

Methods to Duplicate a Worksheet

Method Type
Steps
Remarks
Right-click menu
Right-click sheet tab ➜ Move or Copy ➜ Tick “Create a copy” ➜ OK
Copies within the same workbook
Drag-and-drop
Hold Ctrl ➜ Drag sheet tab ➜ Release
Copies within the same workbook
Ribbon (Format)
Home ➜ Format ➜ Move or Copy Sheet ➜ Tick “Create a copy” ➜ OK
Copies within the same workbook
Move/Copy – existing book
Right-click sheet tab ➜ Move or Copy ➜ “To book” ➜ Select open workbook ➜ Tick “Create a copy” ➜ OK
Duplicates the sheet into another open workbook
Move/Copy – new book
Right-click sheet tab ➜ Move or Copy ➜ “To book: (new book)” ➜ Tick “Create a copy” ➜ OK
Duplicates the sheet into a brand-new workbook
Copy-paste cells
Select All (Ctrl+A) ➜ Ctrl+C ➜ Open/add new sheet ➜ Ctrl+V
Copies cell content only — loses some formatting, print setup, and sheet-level settings
VBA
Alt+F11 ➜ Insert module ➜ Sheets("Sheet1").Copy After:=Sheets(Sheets.Count) ➜ Run
Copies within the same workbook (or into a new one if no destination is set)

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, such as 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 copy an existing workbook without having to follow 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 copying any file on your system. Although it is the quickest way to duplicate a workbook, many Excel users don’t know the method.

  1. First, select the file for which you want to make a copy.
    select-the-file
  2. After that, use the keyboard shortcut, Ctrl + C to copy that file.
    copy-the-file
  3. After that, use the keyboard shortcut Ctrl + V to paste that file to the same location.
    paste-the-file
  4. In the end, edit the name of the file and change it to whatever you want to specify.
    change-the-file-name

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:

  1. 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.
    open-the-excel-file
  2. 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.
    select-open-as-copy
  3. 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.
    save-as-the-file
  4. 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 Sub

This 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.

Related Tutorials