List worksheets as named ranges

Brief description:

List all worksheets as a named range using cell ‘A1’.

Full description:

List all worksheets as a named range using cell ‘A1’.

Each worksheet is listed as a named range which can make access quicker and easier.

Spaces in the worksheet name are replaced with an underscore. Each worksheet is appended with a ‘WS_’ string for easy identification.

For example: Running this routine on a workbook with six worksheets produced the following list…

Hidden worksheets are not shown if selected.

To use: Run the macro. Then worksheets are available from the named range dropdown box at the top left of Excel.

NOTE: This can be convenient but is less useful if there are a lot of named ranges or worksheets in use.

Code:

Sub ListWorksheetsInNamedRanges()
    'Procedure to list all worksheets as named ranges for easy access
    
    Dim CurrWS As Worksheet
    Dim ThisWorkBook As Workbook
    
    Set ThisWorkBook = ActiveWorkbook
    
    For Each CurrWS In ThisWorkBook.Worksheets
        ThisWorkBook.Names.Add _
            Name:="WS_" & Replace(CurrWS.Name, " ", "_"), _
            RefersTo:="='" & CurrWS.Name & "'!" & CurrWS.Range("A1").Address
    Next CurrWS
    
End Sub