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

List All Sheets inside Excel Workbook

How to create and display a List of all the Worksheets in the Workbook in Excel using formula

In Excel, we can by using the Formula or using VBA macro to create a list of names of all the worksheets contained inside the workbook.

An example of the names of all the worksheets in the workbook by using the formula. In the workbook specify a single worksheet in which you create a list of names of all the worksheets in the active workbook (see image below).


How to create a list of names of all the worksheets that are inside workbook using the formula

Click on the tab 'Formulas => Name Manager => New Name'

Enter in the field Name: SheetIndex


Place the following formula in the field Refers to:
=REPLACE(GET.WORKBOOK(1)&T(NOW());1,FIND("]";GET.WORKBOOK(1)&T(NOW()));"")

How using the Name Manager to create an auxiliary formula for a list of all the worksheets in a workbook


The following formula below set in the first cell list and copy down. (source ozgrid.com)

=IF(ROW(A1)-ROW($A$1)+1<=COUNTA(SheetIndex);INDEX(SheetIndex;ROW(A1)-ROW($A$1)+1);"")

Making a list of worksheet names inside active workbook using VBA macros

Using VBA macros can create a list of all the worksheets within a workbook. VBA macros copy the modules as follows. (source Internet Netwok)


VBA example 1

Copy this VBA macro in Module1 under your workbook.

Sub ListAllSheets()
'list of worksheets inside workbook
'http://learn-free-excel-2013-for-beginners.blogspot.com

Dim ws As Worksheet
Dim x As Integer
x = 1
Sheets("Sheet1").Range("A:A").Clear
For Each ws In Worksheets
Sheets("Sheet1").Cells(x, 1) = ws.Name
x = x + 1
Next ws
End Sub


VBA example 2

Copy this VBA macro in Module1 under your workbook.

Sub ListAllNamesOfWorksheets()
'list of worksheets inside workbook
'http://learn-free-excel-2013-for-beginners.blogspot.com

Dim i As Integer, sh As Worksheet, ws As Worksheet
Set ws = Sheets("SheetName")
i = 2
For Each sh In Worksheets
ws.Cells(i, 1) = sh.Index
ws.Cells(i, 2) = sh.Name
i = i + 1
Next   
End Sub


VBA example 3

Copy this VBA macro below in the ThisWorkbook Module of your workbook.

Public Sub ListAllWorksheets()
'list of worksheets inside workbook
'http://learn-free-excel-2013-for-beginners.blogspot.com

Dim x As Integer
Dim ws As Object
Sheets("BaseSheet").Columns("B:B").Delete Shift:=xlToLeft ' Sheet range
For Each ws In Sheets
x = x + 1
Sheets("BaseSheet").Range("B" & x).Value = ws.Name
Next ws
End Sub


VBA example 4

Copy this VBA macro below in the ThisWorkbook Module of your workbook. List is created on Sheet1

Sub ListAllSheets()
'list of worksheets inside workbook
'http://learn-free-excel-2013-for-beginners.blogspot.com

Dim i As Integer
For i = 1 To Sheets.Count 'Includes chart sheets
Sheets(1).Cells(i, 1) = Sheets(i).Name
Next i
End Sub


How to display the list, names, and order of sorted worksheets in the workbook.

In this part of the tutorial, I will show the layout and order of all worksheets in the active workbook. In the image below you can see the created worksheets as well as their names and sorted positions in the order of the active workbook. The purpose of this tutorial is to display worksheets names that are chronologically created in the Excel Workbook and also if we moved the worksheets then we can see the differences in placement and order.

Notice in the cells 'A6:F6', the blue numbers that indicate the ordinal numbers for all created worksheets that Excel has assigned to each newly created worksheet. In the red box below, find out how the order of created worksheets looks like, on which we want to display worksheets in cells 'A5: F5'.

The formula in cell 'A5' is the following (we copy this formula to the right). This formula based on the number in the cell above (red color) as a result returns the name of the worksheet. So, the formula looks at the number in the cell above and returns the name for the first sheet of work. Notice in the cells 'A4:F4' the red numbers that are the order numbers of sorted worksheets in the active workbook.

=Sheet(A4)

Formula '=Sheet(D4)' as a result returns the '6th-sheet' workbook because this worksheet is in the fourth place since the beginning of the first worksheet in the active workbook. Although this worksheet Excel has created as the sixth creation (see the red box in the image above).

The UDF function, i.e. VBA macro, for rendering the order and sorting of the worksheets is the following. Copy this UDF function into your active workbook's Module1.

Public Function Sheet(wsIndex As Long) As String
'the name of the active worksheet in relation to the order of creation
'formula in cell =Sheet(cell address)
Application.Volatile
Sheet = Worksheets(wsIndex).Name
End Function



How to display the names and order of sorted worksheets in a workbook