Sheet Names

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a function that I can use to list all of the active workbook tabs names ?
I am trying to have a data validation tab, which uses a list of workbook tab names as the scroll down.
 
A real simple macro that runs through the sheetnames

List names of sheets down from active cell (#sheetnaemsdownrows)
Sub SheetNamesDownRows()
Dim iSheet As Long
For iSheet = 1 To ActiveWorkbook.WorkSheets.Count
ActiveCell.offset(iSheet - 1,0) = "'" & WorkSheets(iSheet).Name
Next iSheet
End Sub

Note that the active cell is not being changed in the above.

The above is a macro, if not familiar with macro, run it from an
empty page, as it will generate data down from the current cell.
Information on Getting started with Macros
http://www.mvps.org/dmcritchie/excel/getstarted.htm

For something with more information in it you can look at my
Build Table of Contents pages (buildtoc.htm and buildtoc2.htm)
http://www.mvps.org/dmcritchie/excel/buildtoc.htm

Don't know how you intend to use this for validation, but Debra
Dalgleish has pages at http://www.contextures.com/
has a page on building a validation list from something like the
output from the above.
http://www.contextures.com/xlDataVal05.html
 
Are you aware that XL has one already built in?

Just right click anywhere within the small arrows to the left of the sheet
tabs and you'll get a list of the first 15 sheets.
This list can be expanded to display an infinite scrolling list of *all* the
sheets in the WB, where any sheet can be accessed with a simple "click".

The one shortcoming (?), is that this list is in the exact order that the
sheets are in the WB.
Any sorting must be accomplished my manually moving the sheets themselves
within the WB.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Is there a function that I can use to list all of the active workbook tabs
names ?
I am trying to have a data validation tab, which uses a list of workbook tab
names as the scroll down.
 
MJH

What is the objection to a macro?

''list of sheet names in a workbook - placed on a new worksheet
Sub ShowNames()
Set wkbkToCount = ActiveWorkbook
iRow = 1
With Sheets.Add 'delete Sheets.Add and replace with ActiveSheet
'to list on ActiveSheet
For Each ws In wkbkToCount.Worksheets
.Rows(iRow).Cells(1).Value = ws.Name
iRow = iRow + 1
Next
End With
End Sub

A kludgy manual method.........

Select first sheet. Right-click and "select all sheets"

Select a cell in first sheet. Enter the following...

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

This will return the sheet name on each sheet.

Ungroup the sheets(right-click and "Ungroup sheets")

You can now copy/paste special>values each of the sheet names to one sheet.

More work than just typing a list.

Gord Dibben Excel MVP
 
Back
Top