How to Use Sheet Name Code in Excel (4 Applications)

In this article, we will learn how to use and get sheet name code to excel. In Microsoft Excel, we can use the sheet name directly in a formula as a reference. We will go over different examples with the different datasets to illustrate the process of using sheet name code excel to you.

Download Practice Workbook

You can download the practice workbook from here.

4 Applications of Sheet Name Code in Excel

In this tutorial, we will cover 4 applications of sheet name code excel. We will use formulas, VBA code to interact with sheet name code. Let’s dive into the process.

1. Excel Function to Insert Current Sheet Name Code

First and foremost, we have the following dataset of six salespeople. It tells us the number of sales for January, February, March. In this example, we will find out the sheet name of the current sheet in cell D12. Let’s see how we can do this by following steps:

• In the beginning, select cell D12.
• Insert the formula in that cell:

• Press Enter.
• So, we get the name of the current sheet in cell D12.

🔎 How Does the Formula Work?

• CELL(“filename”,A1))+1,255): This part extracts the full file name and path
• FIND(“]”,CELL(“filename”,A1))+1,255): As the text argument, CELL passes this result to the MID function. Because the sheet name begins right after the left bracket, the FIND function is used to determine the starting location.
• MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,255): Returns the name of the current worksheet. The maximum number of characters to extract is 255. You can’t name a worksheet longer than 31 characters in the Excel UI, but the file format allows for worksheet names of up to 255 characters, therefore the complete name is received.

2. Use Sheet Name Code as Reference in Excel Formula

2.1 Without Spaces or Punctuation Characters

For this example, we will have to look at our previous dataset. The sheet name of our previous dataset is Sales. If you notice that there is no punctuation or spaces in the sheet name. In this example, we will use sheet names without punctuation or spaces as a reference in another worksheet. We will use the INDIRECT function to do this task. The following figure is just an overview of our previous dataset.

• Now, look at the below image. Our purpose for this example is to calculate the total sales amount of January of our previous dataset. We will input the total value in cell C5. Just follow the simple steps to perform this action:

• Firstly, select cell C5.
• Next, Input the following formula in that cell:

• Then, hit Enter.

• Finally, we get the total sales amount for January in cell C5.

• Again we can get the total sales for February by inserting the following formula in cell D5:

🔎 How Does the Formula Work?

• INDIRECT(B5&”!C5:C10″): This part considers the sheet name in cell B5. Then, take the values of the cell range (C5:C10) from that sheet.
• SUM(INDIRECT(B5&”!C5:C10″)): Returns the value of cell range (C5:C10).

2.2 With Spaces or Punctuation Characters

In our previous example, we didn’t have any spaces or punctuations in our sheet name. For this example from the following dataset, we can see that we have a space between two words in our sheet name. The name of the sheet is Sales Data. Let’s see how we can face problems for this space to use this sheet as a reference. Also, we will look at how we can solve this problem following some simple steps.

• Here, we will calculate the total amount of sales for the month of January and February.

• First, select cell C5 and insert the formula that we used in our previous example:

• Press Enter.
• We get ‘#REF!’ error.

Now there might be a question in your mind. We are doing the same process as our previous example. Why is it showing an error? It’s because of the space in the name of the worksheet. Our formula does not read after space. As a result, it does not find any match for the worksheet and shows an error.

• Now to solve this problem insert the following formula. The new formula is slightly different from the previous one.

• Press Enter.
• We can see the total sales amount for the month of January in cell C7.

• Similarly, by inserting the following formula we get the total sales amount for the month of February.

3. VBA to Get Sheet Name Code in Excel

Using VBA(Visual Basics for Applications) code, we can get sheet name code easily. In this section, we will see the use of VBA code to extract sheet names.

3.1 Find Active Sheet Name

We can find the name of the active worksheet with VBA. For this example, we have the following dataset. We will extract the name of this sheet in cell D12. Just follow the simple instructions to perform this action:

Now to step further you will need the Developer tab in the menu bar to make macro-enabled content. If you do not have the Developer tab in your excel menu bar, follow the steps below to get the Developer tab. If you already have the Developer tab in your excel just skip this part and continue from next. Let’s see how we can make the Developer tab visible:

• Go to the File option in the top-left corner of our Excel.

• Next, select the Options.

• Then, a new window will come. Select the option Customize Ribbon from the available options. Select the Developer option and click OK.

• Finally, we can see the Developer tab in our Excel.

Now we will use the Developer tab to create macro-enabled content. Let’s see how we can do this in the following steps.

• Go to the Developer tab. Select the Visual Basic option.

• Here, a new window will open. From the window select the Insert tab. From the drop-down, select the Module option. We will get a new module named Module-1.

• Select the option Module-1. A blank window will open.

• Then Insert the following code in the blank window.

Sub SheetName()
Range("D12") = ActiveSheet.Name
End Sub

• We will Click on the run option that we can see in the below image. Also, we can press F5 to run the code.

• Finally, we can see the name of the active sheet in cell D12.

3.2 Input All Sheet Names in Cells Using VBA

In this example, we will list all the worksheets of a workbook in a single worksheet using VBA code. In the following figure, we can see a worksheet named Sheet_List. We will list down all the worksheet names in column B of this sheet. Let’s see the steps to perform this action:

• Firstly, open a new module for the worksheet.
• Next, insert the following code in the code window:

Sub SheetNames()
Columns(2).Insert
For i = 1 To Sheets.Count
Cells(i, 2) = Sheets(i).Name
Next i
End Sub

• Then click on the Run icon. We can also run the code by pressing the F5 key.

• Finally, we can see the list of all sheets of this workbook in column B.

3.3 Use Code Number to Extract Sheet Name in Excel

We can get the name of a sheet by using the CodeName of that sheet in the VBA code window. We will extract the sheet name of the following dataset by using the CodeName. Just follow the simple instructions to perform this action:

• First, open a new module for the worksheet.
• Next, input the following code in the code window:

Sub SheetName()
MsgBox CodeName.Name
End Sub

• Then click on the Run icon. We can also run the code by pressing the F5 key.

• At last, we can see a new message box showing the name of the active sheet.

Read More: How to Get Excel Sheet Name (2 Methods)

4. Insert Excel Sheet Name Code in Header and Footer

In this method, we will add sheet names in the header and footer section of a worksheet. We will use the following dataset to illustrate this method. Let’s see the necessary steps for performing this action:

• Firstly, go to the Insert tab.
• Next, select the Text option. From the drop-down select the Header & Footer option.

• Then, we will see a header section with a default text &[Tab].

• After that, select the option Sheet Name from the Header & Footer section.

• Finally, we can see the name of the worksheet “Sales Data Header” in the header section.

Now like in this example, we will add the sheet name in the footer section of the following dataset. Let’s see how we can do this:

• First, go to the Header & Footer section.
• Next, select the Footer option.
• Then, select the option Sheet Name.

• Finally, we can see the sheet name “Sales Data Footer” in the footer section of the dataset.

Read More: How to Apply Sheet Name Code in Footer in Excel (3 Ways)

Conclusion

In the end, we have gone through the different methods to use sheet name code excel in this article. To practice yourself download the practice workbook added with this article. If you feel any confusion just leave a comment in the below box. We will try to answer as soon as possible. Stay tuned with us for more interesting solutions to Microsoft Excel problems.

Related Articles

• How to Search Sheet Name in Excel Workbook (2 Methods)
• Select Sheet by Variable Name with VBA in Excel (2 Ways)
• How to Search Sheet Name with VBA in Excel (3 Examples)
• Rename Sheet in Excel (6 Easy and Quick Methods)
• How to Rename Sheet with VBA in Excel (Both Single and Multiple Sheets)
• Use Excel Sheet Name From Cell Value (Three Ways)