Do you need to make a duplicate version of a worksheet? This post is going to show you all the ways you can make a copy of a sheet tab in Google Sheets.

It’s good practice to make a backup copy of your dataset and work on the duplicate. This way, you can easily go to the original version when you need to verify a piece of the data.

Google Sheets makes it easy to create backup copies for these purposes by duplicating sheet tabs to another workbook or into the existing file.

This article will take you through the steps involved in copying any sheet. Get your copy of the example workbook to follow along!

Duplicate a Sheet Tab to the Current Workbook

The process of copying a sheet is quite easy.

Left-click on the options arrow next to the sheet name to which you want to copy. Then choose the Duplicate option from the menu.

You can also right-click anywhere on the sheet name to open the sheet option menu.

This will create an exact copy of the previous sheet with all its contents.

💡 Tip: When you create a copy of a sheet, it will create a default name prefixed with Copy of. You can easily rename the sheet.

Duplicate Multiple Sheet Tabs at the Same Time

You can also make Duplicates of multiple sheets at once.

To duplicate multiple sheets at once, select all the sheets you want to duplicate. Do this by pressing and holding the Ctrl key while using the mouse to select the sheets.

After selecting the sheets, Right-click on any of the selected sheets and click on Duplicate.

This is an easy way to create duplicates of more than one sheet at a go.

Duplicate a Sheet Tab to a New or Existing Workbook

There are two options for copying a sheet. You can either copy it into the current workbook or copy it into another workbook.

Right-click on the sheet and select the Copy to option. In the dropdown option that appears, select New spreadsheet.

This option will create a new spreadsheet that will have a copy of the sheet from the previous spreadsheet.

You can click on the Open spreadsheet option to go to the new spreadsheet.

When you select the Existing spreadsheet option, it opens your google drive. From there, you can select which spreadsheet you want to copy the sheet into.

💡 Tip: You can also create copies of multiple sheets. Hold Ctrl to select multiple sheets and follow the description for duplicating multiple sheets above with the Copy to command.

Duplicate Sheet Tabs with the Sheets Manager Add-on

Add-ons are created by third party developers to further enhance the capabilities of Google Sheets.

The Sheets Manager add-on helps you to easily access controls that affect the sheet structure of your workbook.

To download the add-on, go to the Extensions menu ribbon, select Add-ons and click on Get add-ons. This takes you to the Google workspace market.

In the workspace market, search for Sheets Manager by Ablebits. Click on the Install button.

You will be prompted to grant the necessary permissions needed to use the add-on.

Exit the workspace market. Go back to the Extensions menu ribbon and you will find the Sheets Manager add-on in the dropdown.

Select Sheets Manager and then click Start to open the add-on.

When the Sheets Manager dialogue window will open on the right side of the spreadsheet. You can select the sheet or sheets you want to copy.

Press and hold the Ctrl key while selecting the sheets to select multiple sheets.

Click on the copy icon in the menu tab. You will find three options in the dropdown. The Current spreadsheet option creates a duplicate of the select sheet or sheets in the current spreadsheet. The other options create copies of the selected sheets in a new spreadsheet, or a spreadsheet file located in your google drive.

Duplicate Sheet Tabs with an Apps Script

You may want to make multiple copies of one sheet. None of the solutions described above can accomplish this.

But you can achieve this using a custom app script!

Open the app script editor window by going to the Extensions menu and selecting the Apps Script option.

function copyToNewSheets() {
  var ss = SpreadsheetApp.getActiveSheet();
  var sheetName = ss.getSheetName();
  var ui = SpreadsheetApp.getUi();
  var prompt = ui.prompt("Enter Number of Copies", "Number of Copies", ui.ButtonSet.OK_CANCEL);

  if(prompt.getSelectedButton() == ui.Button.OK) {
    var copies = prompt.getResponseText()
  }

  for(i = 1; i <= copies; i++) {
    var newSheet = SpreadsheetApp.create(sheetName + i);
    var copy = ss.copyTo(newSheet);
    var id = newSheet.getId();
    var del = newSheet.getSheetByName("Sheet1");
    SpreadsheetApp.openById(id).deleteSheet(del);
  }

}

function copyToSameSheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var id = ss.getId();
  var open = SpreadsheetApp.openById(id);
  var active = ss.getActiveSheet();
  var ui = SpreadsheetApp.getUi();
  var prompt = ui.prompt("Enter Number of Copies", "Number of Copies", ui.ButtonSet.OK_CANCEL);

  if(prompt.getSelectedButton() == ui.Button.OK) {
    var copies = prompt.getResponseText()
  }

  for(i = 1; i <= copies; i++) {
    var copy = active.copyTo(open);
    var copyName = copy.getSheetName();
    var name = ss.getSheetByName(copyName);
    newSheetName = name.setName(copyName + i);
  }
}

function onOpen(e) {
  SpreadsheetApp.getUi()
      .createMenu("Make Copies")
      .addItem('To New Spreadsheets', 'copyToNewSheets')
      .addItem('To Same Spreadsheet', 'copyToSameSheet')
      .addToUi();
}

Copy and paste the above code into the app script editor. Hit Save and Run to grant the necessary permissions. Afterward, refresh your spreadsheet.

When the spreadsheet refresh is done, the script creates the Makes Copies menu ribbon.

Make Copies has two menu items.

• To New Spreadsheets
• To Same Spreadsheet

The To New Spreadsheets option duplicates the active sheet to a new spreadsheet. To Same Spreadsheet creates duplicates of the active sheet inside the current spreadsheet.

Both options trigger a pop-up box that allows you to specify the number of copies you want to create.

When you create copies into a new spreadsheet, you can access the newly created file from your Google Drive app.

Conclusions

There are different reasons why you will want to create a duplicate of a sheet tab.

Whatever the reason, you can easily make copies of any sheet using the options described above.

This can even be automated with app scripts to create any number of sheet copies desired.

Do you ever need to make a copy of your sheet tabs? Do you have any other tips for getting this done? Let me know in the comments!