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

Advertisements
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

    http://groups.google.com/group/microsoft.public.excel.worksheet.functions/msg/e803e69927d7164e

    Like

  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
    Ross

    Like

  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: http://www.cpearson.com/excel/Clipboard.aspx

    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
    mText.PutInClipboard
    WriteToClip = Textin & ” copied to clipboard”
    End Function

    Like

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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