Do you need to duplicate a sheet in Excel?

There are a number of useful tips that can save you time and effort. One such trick is learning how to copy a sheet.

At some point, you’ll need to create new sheets in your spreadsheet projects, but creating a new sheet from scratch can be inefficient. Especially if the layout of the new sheet will be similar to an existing sheet.

Fortunately, Excel allows you to easily copy sheets. This way you can duplicate sheets and continue working on them without compromising the original.

This post will guide you through all the ways to copy a sheet in Excel.

Copy a Sheet from the Home Tab

Creating a copy of a sheet can be done from the Home tab of the ribbon command.

All you need to do is select the sheet you want to duplicate and then use the command in the ribbon.

Here are the steps to copy any sheet.

1. Select the sheet you want to copy. You can select the sheet by clicking on the sheet tab in the lower left of the workbook. The active sheet will be the sheet that’s copied.
2. Go to the Home tab.
3. Click on the Format command in the Cells section.
4. Select the Move or Copy Sheet option from the menu. This will open the Move or Copy menu where you can select various options to either move or copy the sheet.

5. Select the location where you would like to create the copy in the To book dropdown list. This will show you a list of all your open workbooks. You can select the current workbook, any other open workbook, or select a new book. Selecting the new book option will open a new workbook and move or copy the sheet to the new book.
6. Select the sheet from the Before sheet section that lists all the sheets in the workbook. This will add the copy to the left of the selected sheet tab. You can also select the move to end option to create the copied sheet to the right of all other sheet tabs.
7. Check the Create a copy option. This will create a copy of your sheet instead of just moving the original to a new location.
8. Press the OK button.

You will now have an exact copy of the sheet in your chosen location!

Copy a Sheet from the Right Click Menu

The command for copying a sheet is a bit hidden in the Home tab, so it will be easier to access this from the menu when you right click on any sheet tab.

Here are the steps to duplicate a sheet from the right-click menu.

• Right click on the sheet you want to copy.
• Select the Move or Copy option from the menu.

This will open the same Move or Copy menu as seen from the Home tab and the process of creating the copied sheet is the same.

Copy a Sheet with a Click and Drag

Using the Move or Copy meniu from either the Home tab or the right click menu is not entirely intuitive.

Thankfully there is an easy trick that will streamline the process quite a bit.

You can use the Ctrl key with a click and drag motion to quickly copy any sheet.

Here are the steps to copy any sheet with a click and drag.

1. Press and hold the Ctrl key.
2. Left-click and drag on the sheet you want to copy. When you click and drag the sheet while holding the Ctrl key you will see a small sheet icon with a plus sign on it next to the mouse cursor. This indicates you are about to make a copy of the sheet.
3. Move the cursor to a new location. You will see there is also a small black arrow that indicates where the new copy will be placed in the sheet tabs.
4. Release the left click to create the copy.

This will create a new copy of the sheet! This is by far the quickest and easiest way to copy a sheet!

💡 Tip: The Ctrl plus click and drag shortcut works for many other objects such as shapes, charts, images, etc. Try it with anything you want to copy!

Copy a Sheet Using VBA

Creating a copy of a sheet can also be done with VBA as part of your automation process.

Sub CopySheets()
    Sheets(Array("Jan", "Feb", "Mar")).Copy After:=Sheets("Mar")
End Sub

This VBA code will copy multiple sheets and insert them into a workbook. The code is written in the VBA Editor, which can be accessed in Microsoft Excel by pressing Alt + F11.

The code will create a copy of the Jan, Feb, and Mar sheets in the workbook and place them after the Mar sheet.

The Sheets(Array("Jan", "Feb", "Mar")) section of the code selects the sheets to be copied: Jan, Feb, and Mar. The Array function is used to group the sheets together in a single command.

The Copy method is then used to copy the selected sheets.

The After:=Sheets("Mar") argument specifies where the copied sheets will be inserted. Specifically, the copied sheets will be inserted after the sheet named Mar.

The After:= code can be replaced with Before:= in the code to place the copied sheets before a given sheet in the workbook.

Copy a Sheet Using Office Scripts

You can also copy a sheet within your Office Scripts automation.

function main(workbook: ExcelScript.Workbook) {
    let ws = workbook.getWorksheet("Mar");
    let wsCopy = ws.copy(ExcelScript.WorksheetPositionType.after, ws);
}

This Office Script code snippet main() function uses the getWorksheet() method to retrieve a worksheet named Mar from the workbook.

It then creates a copy of the worksheet using the copy() method, specifying that it should be placed after the original worksheet using WorksheetPositionType.after. The copied worksheet is stored in the wsCopy variable.

This can also be added after a given sheet using WorksheetPositionType.before.

Overall, this code allows you to create a duplicate worksheet in Excel.

Copy Multiple Sheets at Once

Suppose you need to copy many sheets in your workbook. For example, you might need to copy a sheet from each month of the year.

This would be tedious to do for each sheet individually.

Thankfully, you can copy multiple sheets at once.

Here are the steps you can follow to copy multiple sheets at the same time.

1. Select the sheets you want to copy. Click on the first sheet, then hold the Ctrl key and left click on any other sheets to be copied. This will group the sheets together.
2. Now use the Move or Copy from either the Home tab or the right click menu.

This will open the Move or Copy menu and the process to create a copy will be the same as before. This time a copy of each selected sheet will be created.

Conclusions

It’s clear there are many different ways to copy a sheet in Excel.

Whether through the Home Tab, the right-click menu, a click and drag, VBA, or Office Scripts, users have many options available. You can even copy multiple sheets at once.

With any of these methods, users will be able to quickly and easily create and work with duplicate copies of sheets in their workbooks.

Have you needed to create a copy of your sheets? How did you do this? Comment below and let me know!