copyable list of worksheet names

[arturo]

I'd like to copy a list of the names of all the worksheets in a workbook. File Preferences shows a list of worksheet names but it's view-only text--I can't copy it. Is there a way to get a list I can copy?

 

[Mark W.]

ASAP Utilities, a popular freeware add-in, has an "Information | List all sheet names" menu command. See www.asap-utilities.com. There's hardly a day that goes by that I don't use one of its features!
This message was edited by Mark W. on 2002-04-26 15:28

 

[NateO]

I made use of a function to do this a while back. If you put the following in a standard module:

Function SHEETOFFSET(offset)
Application.Volatile
SHEETOFFSET = Sheets(Application.Caller.Parent.Index _
+ offset).Name
End Function

Now, on the first sheet, if your first sheet is going in row 1 then down, use the following formula:

=IF(ISERR(sheetoffset(ROW()-1)=TRUE),"",sheetoffset(ROW()-1))

and copy down

If it's in row 2:

=IF(ISERR(sheetoffset(ROW()-2)=TRUE),"",sheetoffset(ROW()-2))

and copy down

etc......It will show "" if there's no more sheets to show.

Incidentally, if you want to copy accross instead of down switch "row" with "column" in the worksheet formulas.

A different way to pluck a duck.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-04-26 15:57

 

[Jay Petrulis]

How about a quick VBA solution? Change the sheet referencing to suit.

Sub test()
Dim Counter As Integer, ws As Worksheet
Counter = 1

For Each ws In ThisWorkbook.Worksheets
Sheet1.Cells(Counter, 1) = ws.Name
Counter = Counter + 1
Next ws
End Sub