Our Recommendation for You Search your Query, You can find easily. for example search by book name or course name or any other which is related to your education

Followers

Excel Worksheet Name or Sheet Name Tab

What should we do with the Worksheet name or Sheet Name tab in Excel

In this tutorial, I planned to show that any additional actions we can perform when it comes to the worksheet so I decided on a place to show how we can manipulate "the name of the Worksheet" or "Sheet Name Tab". As you know, each worksheet contains a Sheet Name on the Tab. When you open a new workbook by default within the same there is a three (3) worksheet, of course this setting we can change and define how many worksheets we want to have when you create a new workbook.

By default, these worksheets are named "Sheet1, Sheet2, Sheet3 ..." Each new addition of a new worksheet defines identical name with the next sequence number. Of course, we can also change the name of the worksheet.

In the picture below you see the names of worksheets that have been created in the new Excel workbook.

The blue arrow indicates the names of the worksheets as they were created by the Excel (Sheet1, Sheet2, Sheet3 ...)

The red arrow indicates the names of the worksheets if we personally renamed some of the worksheets. This is important when working with some formulas, UDF functions or VBA macros, because the Excel sometimes pays attention to the name of the worksheet, depending on what we were doing or that formula, function, VBA macro use.

How to know the order created called worksheets in the Excel Workbook

How to automatically display the Sheet Name of the active worksheet in the Excel cell

Right at the beginning of the tutorial I will show how we can display the name of a worksheet in a cell of the active worksheet. ATTENTION! The workbook must be saved on the hard drive that formula worked. For the operation of certain formulas especially UDF/VBA macros need to refresh each change by pressing the F9 key.



In the picture below you will see all the worksheets contained in the workbook. If you want to create a list of all the worksheets in the Excel Workbook then see the tutorial on the respective link.
I was based on the first worksheet that Excel when creating and opening a workbook defined as "Sheet1". I am concerned worksheet renamed "ProductsSheet".

How to display the path to the workbook and display the Sheet Name tab in the Excel cell

In the picture above you can see the next cell containing the formula to display the Sheet Name in the cell. Formulas used Excel function CELL and other combinations of nested functions that return required result.

The formula in cell 'A2' is as follows: the formula returns a path to the active Workbook.

=LEFT(CELL("FILENAME";A1);FIND("]";CELL("FILENAME";A1)))

The formula in cell 'A3' is as follows: Formula as a result as the previous one, but with slight differences returns the path to the active workbook. (formula must be in a single line of code)

=SUBSTITUTE(SUBSTITUTE(LEFT(CELL("FILENAME";A1);FIND("]";CELL("FILENAME";A1)));"[";"");"]";"")

The formula in cell 'A6' is as follows: as a result of the formula returns the name of the Workbook without the path. (formula must be in a single line of code)

=MID(CELL("FILENAME";A1);FIND("[";CELL("FILENAME";A1))+1;FIND("]";CELL("FILENAME";A1))-FIND("[";CELL("FILENAME";A1))-1)

The formula in cell 'A9' is as follows: as a result of the formula returns the Worksheet name and path to the active workbook.

=CELL("FILENAME";A9)

The following formulas below as a result of returning Sheet Name the active worksheet in the workbook. So, the name of the worksheet where the formula is located.

The formula in cell 'A12' is as follows: (formula returns Sheet Name the active worksheet), (formula must be in a single line of code)

=MID(CELL("FILENAME";A1);FIND("]";CELL("FILENAME";A1))+1;LEN(CELL("FILENAME";A1))-FIND("]";CELL("FILENAME";A1)))

The formula in cell 'A13' is as follows: (formula returns Sheet Name the active Worksheet and sometimes it is necessary to initiate a refresh of the formulas to display the correct name of the active worksheet and we do that by pressing F9)

=MID(CELL("FILENAME");FIND("]";CELL("FILENAME"))+1;256)

The formula in cell 'A14' is as follows: (formula returns Sheet Name the active worksheet)

=MID(CELL("FILENAME";A1);FIND("]";CELL("FILENAME";A1))+1;256)

The formula in cell 'A15' is as follows: (formula returns Sheet Name the active worksheet), this formula is identical to the previous but I wanted to show that it does not matter which cell type in the address in the formula.

=MID(CELL("FILENAME";A6);SEARCH("]";CELL("FILENAME";A6);1)+1;256)

The formula in cell 'A16' is as follows: (formula returns Sheet Name the active worksheet and sometimes it is necessary to initiate a refresh of the formulas to display the correct name of the active worksheet and we do that by pressing F9). This formula is similar to the formula in the cell 'A12'. (formula must be in a single line of code)

=MID(CELL("FILENAME");FIND("]";CELL("FILENAME");1)+1;LEN(CELL("FILENAME"))-FIND("]";CELL("FILENAME");1))

The formula in cell 'A17' is as follows: (formula returns Sheet Name the active worksheet and sometimes it is necessary to initiate a refresh of the formulas to display the correct name of the active worksheet and we do that by pressing F9). (formula must be in a single line of code)

=RIGHT(CELL("FILENAME");LEN(CELL("FILENAME"))-SEARCH("]";CELL("FILENAME")))

The formula in cell 'A18' is as follows: (formula returns Sheet Name the active worksheet). (formula must be in a single line of code)

=RIGHT(CELL("FILENAME";A18);LEN(CELL("FILENAME";A18))-FIND("]";CELL("FILENAME";A18)))

Name of the sheet in cell 'A20': (a result in this cell is created with the help of VBA macros). To display the Sheet Name of the worksheet in cell 'A20' run VBA macro called "SheetNameInCell". If you want to select a cell on the worksheet, and in a selected cell set Sheet Name of the worksheet, do the following. Select the cell in VBA macro code below, remove the comment before the second last line of code and post a comment on the second line of code.
This formula below is related to the VBA macro and set the "Module1" of active workbook, you can run the Alt+F8 or VBA button.

Sub SheetNameInCell()
Range("A20").Value = ActiveSheet.Name 'specified cell in which we set sheet name of the worksheet
'or
'ActiveCell.Value = ActiveSheet.Name 'selected cell in which we set sheet name of the worksheet

End Sub

 

How to change the name or rename Sheet Name of the active worksheet by entering sheet name in the Excel cell A1

In the following part of this tutorial, I will show how we can change the name of the worksheet if you type "sheet name" in the cell of the active worksheet. The condition for the worksheet name will be found in the cell 'A1'. So we are on the active worksheet and we want to enter a new sheet name of the active worksheet, in addition we want to Excel automatically renames the worksheet name in the Sheet Name tab (see figure below).

How to change the name of the worksheet by entering the name in the Excel cell
When we write some text or data in a cell 'A1' automatically rename the worksheet name in the Sheet Name tab. VBA macro code to change the name of the worksheet by entering the cell 'A1' is below. You notice in the picture above that all changed their formula results after I entered the new name of the active worksheet in a cell 'A1'. Of course in the cell 'A20' did not change the result because it is tied to a VBA macro.

For one sheet (copy in VBE in each sheet module)

Private Sub Worksheet_Change(ByVal Target As Range)
'rename a worksheet by entering the name in cell A1 based on the active worksheet
If Target.Address = "$A$1" Then 'cells where we want to set the name of the worksheet
ActiveSheet.Name = [A1] 'cells where we want to set the name of the worksheet
End If
End Sub


For all sheets (copied to the VBE in the ThisWorkbook module)

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'rename a worksheet by entering the name in cell A1 based on the entire Workbook
If Target.Address = "$A$1" Then 'cells where we want to set the name of the worksheet
ActiveSheet.Name = [A1] 'cells where we want to set the name of the worksheet
End If
End Sub

Display in cell sheet name of the active worksheet using VBA in Excel function (UDF)

The formula in cell 'A19' is as follows: (formula returns the name of the active worksheet and sometimes it is necessary to initiate a refresh of the formula to display the correct name of the active worksheet and we do that by pressing F9).

=SheetName()

This formula is related to the UDF/VBA macro and set the 'Module1' of the active workbook and UDF code you see below

Public Function SheetName() As String
'formula in cell of the active worksheet is '=SheetName()' without apostrophe
Application.Volatile
SheetName = ActiveCell.Worksheet.Name
End Function


Why do we need to refresh some formula? Because, the transition from one workbook to another.
Consider the situation that we are working in parallel on multiple open workbooks or worksheets. If the first book, put this formula and switch to another workbook that contains the same or similar formula to load a worksheet names, then the formula to load a different worksheet name and then will keep Excel Sheet Name from the first workbook. Then we need to press F9 to refresh the Excel formula again and load the names of worksheets currently active workbook.

Fixing, worksheet name in a cell for use in the same INDIRECT function and formula

The average user Excel programs likely to be encountered at times to be fixed worksheet name (define it as text) that he could are using in other formulas, functions and the like. This fixation as in the cells 'A23' and 'A24' is often used in the function INDIRECT as an argument. Here are some examples below where we use the results using the formula from cell 'A23' and 'A24'.

syntax: =INDIRECT(ref_text)
formula: =INDIRECT(ShName&"range")

syntax: =VLOOKUP(lookup_value;table_array;col_index_num;range_lookup)
formula: =VLOOKUP(Criteria;INDIRECT(ShName&"range");Column_Num;FALSE)

syntax: =SUM(number1;...)
formula: =SUM(INDIRECT(ShName&"range"))

syntax: =SUMIF(range;criteria;sum_range)
formula: =SUMIF(INDIRECT(ShName&"range");criteria;INDIRECT(ShName&"range2"))

In the formulas above in this part of the tutorial you will see named cell for which I have defined name "ShName". Thus, cells containing the data as a result of these two formulas below may be appointed as the biit easier to work with her and simpler to use the functions within the Indirect.

Further follow formulas that return the name of the worksheet as text and with it additional elements for use in the formula, especially when you are done copying formulas.

The formula in cell 'A23' is as follows:

="'"&MID(A9;FIND("]";A9)+1;256)&"'!"

The formula in cell 'A24' is as follows:

="'"&MID(CELL("FILENAME";A:A);FIND("]";CELL("FILENAME";A:A))+1;256)&"'!"

Fixing Excel cells for further calculations

The example below shows how we can fix a particular cell for further calculation and copy the formula.

The formula in cell 'A11' is as follows: (formulas as a result returns the name of the active worksheet)

=MID(CELL("FILENAME";A1);FIND("]";CELL("FILENAME";A1))+1;256)

The formula in cell 'A12' is as follows: (formula returns a text ie. The name of the worksheet prepared for implementation in the following formula for calculation. This cell is defined by the name ShName example: =INDIRECT(ShName&"range").

="'"&A11&"'!"

The cell 'A15' - In this cell there is a condition for which we want to result from a range of data "A1:E7'

The formula in cell "B15" is as follows:

=VLOOKUP(A15;'2015'!A1:E7;2;FALSE)

The formula in cell "C15" is as follows:

=VLOOKUP(A15;INDIRECT(A12&"$A$1:$E$7");2;FALSE)

The formula in cell "D15" is as follows:

=SUM(INDIRECT(ShName&"B1:B7"))

How to reference the name of the worksheet in Excel cell

How to in one step, rename all the worksheets in Excel Workbook

In the following example you note the image below. This part of the tutorial refers to multiple rename all worksheets contained in the workbook. VBA macros below can be used in situations where we have multiple worksheets that contain a similar name with some minor differences.

This situation I had in the workbook where the twelve worksheets containing year as part of a month and the difference on each worksheet is no month of the year in the name of the worksheets. Thus, by using these VBA macros I can in one step to change the year 2015 to year 2016 at once.

However, let us return to our example and the pictures below.

 Find and Replace specific text in the worksheet name and  modify all Sheet Name at once

This VBA macro bellow, for partial change the name worksheets within a workbook copy in VBE 'Module1' of the active workbook and run a VBA macro via ALT+F8 or Button

Sub MultipleChangeSheetNameTabs()
'multiple changing parts of worksheet name
Dim iSheet As Worksheet
Dim sText As String
Dim rText As String
sText = InputBox("Enter search text")
rText = InputBox("Enter replacement text")
For Each iSheet In ThisWorkbook.Sheets
If InStr(1, iSheet.Name, sText) > 0 Then
Select Case InStr(1, iSheet.Name, sText)
Case 1
iSheet.Name = rText & Mid(iSheet.Name, Len(sText) + 1, Len(iSheet.Name) - Len(sText))
Case Len(iSheet.Name) - Len(sText) + 1
iSheet.Name = Mid(iSheet.Name, 1, Len(iSheet.Name) - Len(sText)) & rText
Case Else
iSheet.Name = Mid(iSheet.Name, 1, InStr(1, iSheet.Name, sText) - 1) & rText & Mid(iSheet.Name, InStr(1, iSheet.Name, sText) + Len(sText))
End Select
End If
Next iSheet
End Sub


This is another way using VBA macro, where we can find part of Sheet Name on all worksheets name tab and replace with new text at once. In a similar way as in the previous part of this tutorial, a partial change the name of all the worksheets execute using VBA macros. This VBA macro copy in 'Module1' active Workbook and run a VBA macro via ALT+F8 or Button.

Sub MultipleWorksheetNameChange()
'find specific text in the name of all worksheets name tab and replacing it with a new text
Dim ws As Worksheet
Dim FindWhat As String, ReplaceWith As String
FindWhat = Application.InputBox("Find What?", "Search for Text", Type:=2)
If FindWhat = "False" Then Exit Sub
ReplaceWith = Application.InputBox("Replace with what?", "Replace Text", Type:=2)
If ReplaceWith = "False" Then Exit Sub
On Error Resume Next
For Each ws In Worksheets
ws.Name = Replace(ws.Name, FindWhat, ReplaceWith, vbTextCompare)
Next ws
On Error GoTo 0
MsgBox "Done!", vbInformation, "Name Change Complete"
End Sub
How to find the same parts in the name of worksheets and replace them with a new text

Opens a new dialog window (pop-up message) in which to enter new text or data with which we want to replace the existing text in the names of worksheets. After you enter a new text by clicking the OK button.
How to quickly rename or change parts of Worksheet names in the Workbook

Finally, the result of VBA macros are new Sheet Names of all worksheets in active workbook. After changing the name of all the worksheets in a single step, the VBA macro can delete.

Completion of VBA macros and multiple modified names of all worksheets


How to copy and duplicate a specific worksheet in multiple copies in the same workbook, and all worksheets, appoint the names from the list in column


In this example, which is linked to the names of the worksheets I will show how we can using VBA macro to copy and duplicate one worksheet into multiple worksheets and each worksheet assign a specific name to the list of worksheets in the column 'A1' basic worksheet that is here shown as 'master'.

You notice in the picture below two worksheets. Worksheet 'master' is essential to containing the list of names in column 'A', ie. worksheets names that we want to assign the copied worksheets.

This example. We can use once we have the data and the table you want to use multiple worksheets and later change key data on each worksheet. So worksheet 'ProductsSheet' contains certain tables and formulas.
I want to copy this worksheet into multiple sheets to contain identical tables and formulas that will change later. This option example. I could use for the tutorial on managing the household budget, because I was 12 identical worksheets with identical formulas with little difference in some cells.

How to copy multiply one worksheet and assign the copied work sheet name from the list in column
In the picture below you see the final result after running a VBA macro below.

Several times copied worksheets with the names from the list in column

After starting the VBA via ALT+F8 appears in pop-up message or dialog box to copy a worksheet and assign a new Sheet Name, it is necessary to confirm by clicking on the OK button. Given that I have listed seven names, this message will appear seven times.

Sub DuplicateSpecificSheets()
'Copy or duplicate "ProductsSheet" sheet or the first sheet after the worksheet 'master' and assign them names from the list of column A
Dim red As Integer
Dim ime As String
Sheets("master").Select
red = 1
ime = Sheets("master").Cells(red, 1).Value 'active sheet that contains the list of sheets for duplication
While ime <> ""
MsgBox red & " " & ime
Sheets(red + 1).Select
Sheets(red + 1).Copy After:=Sheets(red + 1)
ActiveWorkbook.Sheets(red + 2).Name = ime
red = red + 1
ime = Sheets("master").Cells(red, 1).Value
Wend
End Sub

(all formulas and VBA macros, has been compiled from various sources internet network webs or forums in one tutorial; ozgrid.com, mrexcel.com, elitesecurity.org, excelguru.ca, excelforum.com,etc...etc...)