Assignmentgeek.com - makes your homework shine. # How to List All Sheets in 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). Click on the 'Formulas tabtab => 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()));"") 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 you can create a list of all the worksheets within a workbook. VBA macros copy the modules as follows. (source Internet Netwok)

Excel VBA example 1

Copy this VBA macro in Module1 under your workbook.

Sub ListAllSheets()
'list of worksheets inside workbook
'http://www.ic-ims.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

Excel VBA example 2

Copy this VBA macro in Module1 under your workbook.

Sub ListAllNamesOfWorksheets()
'list of worksheets inside workbook
'http://www.ic-ims.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

Excel VBA example 3

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

Public Sub ListAllWorksheets()
'list of worksheets inside workbook
'http://www.ic-ims.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

Excel 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://www.ic-ims.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 of Names Tabs, and Order of Sorted Worksheets in the Workbook.

In this part of the tutorial, I will show the layout 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 