Listing sheet names

Answering my own question from the previous post, here is a short UDF that will return a list of worksheet names, starting with any chosen sheet:

Function Sheetnames(Srange As Range, Numsheets As Long) As Variant
Dim NameA() As String, Firstsheet As Long, i As Long, j As Long
ReDim NameA(1 To Numsheets, 1 To 1)
Firstsheet = Srange.Worksheet.Index
For i = 1 To Numsheets
j = i + Firstsheet - 1
NameA(i, 1) = Worksheets(j).Name & "!"
Next i
Sheetnames = NameA
End Function

Enter as an array formula (with ctrl-shift-enter)

SheetNames() UDF

SheetNames() UDF

This entry was posted in Excel, UDFs, VBA and tagged , , , . Bookmark the permalink.

3 Responses to Listing sheet names

  1. Harlan Grove says:

    There’s an alternative approach using defined names with some of those names calling XLM functions. See


  2. Ross says:

    Hi Doug,
    I think I would have spat it in to one cell, with a “,” between names, what happens if I don’t know how many sheets I have?
    Is it possible to get a UDF to write data to the windows clipboard? That way you could run the function then paste it onto the sheet – if you see what I mean?

    Good stuff


  3. dougaj4 says:

    Ross – you don’t like array functions?

    The point of the sheetname function was to get all the sheetnames listed in a column, so writing them to a column array seemed like the obvious way to do it.

    I’ve never tried writing to the clipboard before, but looking into it, it seems to have interesting possibilities.

    Everything you need to know here:

    The main thing being you need a reference to the Microsoft Forms 2.0 Object Library to make the code below work (I guess you’ll have that anyway, but I’ve just got a new computer and it didn’t have the reference set):

    Function WriteToClip(Textin As String) as string

    Dim mText As DataObject
    Set mText = New DataObject
    mText.SetText Textin
    WriteToClip = Textin & ” copied to clipboard”
    End Function


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s