Naomi would like to reorder the worksheets in a workbook. what is the easiest method of doing this?

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

()

This article demonstrates how to add, remove and reorder worksheets inside a workbook.

Add Worksheets

Adding a new worksheet to a workbook can be easily achieved through its Worksheets collection. The collection exposes an Add() method that does not take arguments and returns the instance of the newly created worksheet. By default worksheets are assigned the first available name in the sequence Sheet1, Sheet2, Sheet3… You can easily change the name of the worksheet through the Worksheet.Name property. More information about renaming a worksheet is available in the Rename a Worksheet article.

Example 1 creates a workbook from scratch and adds a single worksheet to it. Since this is the first worksheet in the workbook, it is also set as the active worksheet. All worksheets added after it will not become active.

Example 1: Create a workbook and add a worksheet to it

Workbook workbook = new Workbook(); Worksheet newWorksheet = workbook.Worksheets.Add();

The Worksheets collection of the workbook offers two methods for removing worksheets: Remove() and RemoveAt(). The former method requires the worksheet name or the worksheet instance to be passed as an argument. The latter allows you specify the index of the worksheet you would like to remove.

Example 2 creates a workbook and adds four worksheets. All worksheets are with their default names: Sheet1, Sheet2, Sheet3 and Sheet4. The code further demonstrates how to remove three worksheets using all of the aforementioned remove methods.

Example 2: Add and remove worksheets

Workbook workbook = new Workbook(); workbook.Worksheets.Add(); // Sheet1 Worksheet secondWorksheet = workbook.Worksheets.Add(); // Sheet2 workbook.Worksheets.Add(); // Sheet3 workbook.Worksheets.Add(); // Sheet4 workbook.Worksheets.RemoveAt(3); // Removed Sheet4 workbook.Worksheets.Remove("Sheet1"); // Removed Sheet1 workbook.Worksheets.Remove(secondWorksheet); // Removed Sheet2 // the only worksheet left is Sheet3

Reorder Worksheets

If you would like to change the order the worksheets appear inside the workbook, you can use the Move() method of the Sheets collection. The method allows you to move one or more consecutive sheets to a specified position. In Example 3, you can see how you can insert 4 sheets and move the last one to the first position in the collection. When the workbook is visualized, the fourth sheet will be the first one visible in the sheet tabs.

Example 3: Add and reorder worksheets

Workbook workbook = new Workbook(); workbook.Worksheets.Add(); // Sheet1 workbook.Worksheets.Add(); // Sheet2 workbook.Worksheets.Add(); // Sheet3 workbook.Worksheets.Add(); // Sheet4 workbook.Sheets.Move(3, 1, 0); // Move the fourth sheet to the first place

Excel relies on a number of rules for displaying sheets, but you can put them in the order you like with just a few quick clicks.

When you add an worksheet, Excel positions it between the currently selected sheet and the previous sheet. Regardless of how careful you are adding new sheets, you might want to rearrange them. To do so, simply right-click the tab you want to move and select Move Or Copy from the resulting context menu. Then, in the Before Sheet dialog box, select the appropriate sheet or select the (Move To End) option. For instance, the sheets in the workbook shown below represent the first three months of the year, but they aren’t in the customary order: January; February; March.

You can quickly position them in the traditional order:

Right-click March and choose Move Or Copy.
In the resulting Move Or Copy dialog box, select (Move To End) and click OK.
Right-click January and choose Move Or Copy.
Select February and click OK.

With just a few clicks, you can quickly reposition sheets.

Published: August 11, 2009, 5:00 PM PDT Modified: August 11, 2009, 5:00 PM PDT See more Software

You can use the Move or Copy Sheet command to move or copy entire worksheets (also known as sheets), to other locations in the same or a different workbook. You can use the Cut and Copy commands to move or copy a portion of the data to other worksheets or workbooks.

Select the worksheet tab, and drag it to where you want it.

Caution: When you move a sheet to another workbook, check any formulas or charts that refer to data on the sheet because moving the sheet might cause errors or produce unintended results in your data. Similarly, if you move a sheet that is referred to by 3-D references, the calculation might include or leave out data on the sheet.

Press CTRL and drag the worksheet tab to the tab location you want.

Right click on the worksheet tab and select Move or Copy.
Select the Create a copy checkbox.
Under Before sheet, select where you want to place the copy.
Select OK.

When you need to reorganize your data, you can easily change the order of sheets in a workbook, or move or copy a sheet to another workbook. But be aware that calculations or charts that are based on sheet data may become inaccurate if you move the sheet.

Drag the sheet tab to the location that you want along the row of sheet tabs.

Hold down OPTION.
Drag the sheet tab to the location that you want the copied sheet to appear along the row of sheet tabs.

Important: Release the mouse button before you release the OPTION key.

Caution: When you move a sheet to another workbook, check any formulas or charts that refer to data on the sheet because moving the sheet might cause errors or produce unintended results in your data. Similarly, if you move a sheet that is referred to by 3-D references, the calculation might include or leave out data on the sheet.

Open the workbook that you want to move the sheet to.
On the Window menu, click the workbook that contains the sheet that you want to move.

Click the sheet that you want to copy to the other workbook.
On the Edit menu, click Sheet > Move or Copy Sheet.
On the To book menu, click the workbook that you want to move the sheet to.

Tip: To create a new workbook that contains the moved sheet, click new book.
In the Before sheet box, click the sheet that you want to insert the moved sheet before, or click move to end.
Click OK.

Open the workbook that you want to copy the sheet to.
On the Window menu, click the workbook that contains the sheet that you want to copy.

Click the sheet that you want to copy.
On the Edit menu, click Sheet > Move or Copy Sheet.
On the To book menu, click the workbook that you want to copy the sheet to.

Tip: To create a new workbook that contains the moved sheet, click new book.
In the Before sheet box, click the sheet that you want to insert the copied sheet before, or click move to end.
Select the Create a copy check box.
Click OK.

Coloring the sheet tabs can help you keep track of your information in a large workbook.

Hold down CONTROL and click the sheet tab.
Click Tab Color, and then click the color that you want to use.

In Excel for the web, you can duplicate (or copy) worksheets within the current workbook. Simply right-click the tab name at the bottom of the sheet and click Duplicate.

Note: You may receive an error when you are trying to duplicate a worksheet that contains a chart, picture, or shape. If you get an error, you can duplicate the sheet manually.

If you have elements in your worksheet that interfere with duplicating it, you can still create a copy of the worksheet manually by copying all the data and pasting it into a new sheet. Here's how:

Select all the data in the worksheet.

Keyboard shortcut: Press CTRL+Spacebar, on the keyboard, and then press Shift+Spacebar.
Copy all the data on the sheet by pressing CTRL+C.
Click the plus sign to add a new blank worksheet.
Click the first cell in the new sheet and press CTRL+V to paste the data.

Note: You will lose any conditional formatting applied to the cells when you paste them into the new worksheet.

In Excel for the web, the option to move or copy a sheet isn’t available when you right-click a sheet tab, as it is in the Excel desktop application. In Excel for the web, you can achieve the same result by copying all of the data in the worksheet and pasting it into a blank worksheet in the other workbook.

Follow these steps to move or copy a worksheet to another workbook in Excel for the web:

Select all the data in the worksheet.

Keyboard shortcut: Press CTRL+Spacebar and then press Shift+Spacebar.
Copy all the data on the sheet by pressing CTRL+C.
Open the workbook in which you want to paste the data, then click the + in the status bar to add a new blank worksheet.
Click the first cell in the new worksheet, then press CTRL+V to paste the data into that worksheet.

Note: You will lose any conditional formatting applied to the cells when you paste them into the other workbook.