Rename sheet based on cell value Google Sheets

Skip to main content

This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

Step by step: Rename Excel sheet with cell contents by using Macro

• Article
• 07/21/2022
• 2 minutes to read
• Applies to:Excel for Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003

In this article

This article was written by Raddini Rahayu, Microsoft MVP.

Case

Here are weekly sales activity reports. In example, there are 15 people in this case. Nancy, the admin reports, is assigned summarizing each sales data into one file where all sales are separated on each sheet. For easily arranging data, each sheet Nancy gave the name matches the sales name on that sheet. At first, Nancy feels comfortable with this, but as more data and demands rapid processing, Nancy was so overwhelmed. To overcome this, Nancy wants to the name sheets changed automatically according to the sales name on each sheet without manually rename it.

Solutions

The best solutions to overcome the Nancy's problem is by using macro. This macro designed for each sheet in that file regardless of the number of sheets. The name of each sheet will change according to the sales name that has been determined in the same location on each sheet.

How to execute

First step

• Designing report formats and specify the cell location where the sales name will be placed. In this case, the location is at cell J2. This report format should be the same on each sheet.

• Keep the sheet names on the default name (Sheet1, Sheet2, Sheet3, etc).

  

Last step

Macro coding step

After we know the cell location where the sales name will be placed, now we can continue the next step, Macro coding.

1. On the Developer tab, select Visual Basic in Code category, or press key combination Alt+F11 on the keyboard, so Visual Basic window is displayed.

   

2. On the project task pane click (Workbook Name), then on Insert menu select Module and write the following script:

   Sub RenameSheet()
   
   Dim rs As Worksheet
   
   For Each rs In Sheets
   rs.Name = rs.Range("B5")
   Next rs
   
   End Sub
   

   

3. Press the F5 key on the keyboard, if there is no debug close the Visual Basic window and return to Excel. If there is debugged, check back your script.

4. Return to Excel and see what happens, if the codes are correct, now the name of each sheet is renamed in accordance with the existing sales name on each sheet.

   

That's it. Hopefully usefully.

Third-party information disclaimer

The third-party products that this article discusses are manufactured by companies that are independent of Microsoft. Microsoft makes no warranty, implied or otherwise, about the performance or reliability of these products.

Additional resources

Additional resources

In this article

Lesson 11: Working with Multiple Sheets

/en/googlespreadsheets/formatting-cells/content/

Introduction

A Google spreadsheet can contain one or more sheets. When working with a large amount of information, you can create multiple sheets to help organize your spreadsheet and make it easier to find information.

In this lesson, you'll learn how to create, rename, move, delete, and duplicate sheets.

Using multiple sheets

When you create a new Google spreadsheet, it has one sheet, which is named Sheet1 by default. In the sheets toolbar located at the bottom of the window, you will see a tab for each sheet you have. To organize your spreadsheet and make it easier to navigate, you can create, rename, delete, move, and duplicate sheets.

Watch the video below to learn how to create and manage multiple sheets.

To create a new sheet:

In our example, the sheets of our service log are organized by month. We'll create a new sheet in the log so data can be entered in the new month.

1. Click the Add Sheet command in the sheets toolbar.
   

2. A new sheet will appear in the sheets toolbar.
   

Alternatively, you can create an additional sheet by clicking Insert and selecting New sheet from the drop-down menu.

To rename a sheet:

1. Click the tab of the sheet you want to rename. Select Rename... from the menu that appears.
   

2. Type the desired name for the sheet.
   

3. Click anywhere outside of the tab or press Enter on your keyboard when you're finished, and the sheet will be renamed.
   

To switch to a different sheet:

1. Click the desired sheet tab in the sheets toolbar.
   

2. The selected sheet will appear.
   

If you want to limit collaborators from editing specific sheets of your spreadsheet, you can protect these sheets by clicking the desired sheet tab and selecting Protect sheet... from the menu that appears.

To move a sheet:

1. Click and drag the tab of the sheet you want to move.
   

2. Release the mouse to place the tab at the desired location.
   

To duplicate a sheet:

1. Click the tab of the sheet you want to duplicate, then select Duplicate from the menu that appears.
   

2. A duplicate of the sheet will appear in the sheets toolbar. It will be named as a copy of the original sheet, such as Copy of May. If you want, you can rename the sheet.
   

To copy a sheet to another spreadsheet in Google Drive, click the tab of the sheet you want to copy, then select Copy to... from the menu that appears. Select the spreadsheet where you want to place the copy from the list that appears. A duplicate of the sheet will appear in the other spreadsheet.

To delete a sheet:

1. Click the tab of the sheet you want to delete. Select Delete from the menu that appears.
   

2. A warning dialog box appears. Click OK, and the sheet will be deleted.
   

Challenge!

1. Open our example file. Make sure you're signed in to Google, then click File > Make a copy.
2. Delete the sheet titled Sheet 1.
3. Duplicate the January sheet and rename it May.
4. Move the sheet you just renamed to the right of the April sheet.
5. Create a new sheet and rename it June.
6. Open the May sheet.
7. When you're finished, your spreadsheet should look something like this:
   

/en/googlespreadsheets/creating-simple-formulas/content/