Most assuredly one could manually type the sheetnames and name that range.

If I had a multitude of sheetnames I would prefer an automatic solution.


Gord


On Fri, 27 Aug 2010 08:41:02 -0700, CellShocked
<cellshocked@thecellvalueattheendofthespreadsheet. org wrote:

Is there not a way to simply apply the sheet names to a columnar cell
array, name it, and make a validation criteria call to the named range?

Then, all one would have to do is maintain the sheet name list.

No VB required


On Wed, 25 Aug 2010 20:01:00 -0700, Gord Dibben wrote:

Run this macro which adds a new sheet named "List".

All existing sheetnames will be listed in column A in this new sheet.

Use this list as the source for your DV dropdown.

Private Sub ListSheets()
'list of sheet names starting at A1
Dim rng As Range
Dim i As Integer
Worksheets.Add(After:=Worksheets(Worksheets.Count) ).Name = "List"
Set rng = Range("A1")
For Each Sheet In ActiveWorkbook.Sheets
If Sheet.Name < "List" Then
rng.Offset(i, 0).Value = Sheet.Name
i = i + 1
End If
Next Sheet
End Sub


Gord Dibben MS Excel MVP


On Wed, 25 Aug 2010 09:47:03 -0700 (PDT), DCG-jaeson
wrote:

Hi Crisso,

Thanks for the concern, this look great...But the line " ' add to
your data validation list here " confuse me what to do. Maybe this
will clear my problem, I need to put a list in Cell A1 Dropdown all
the WorkSheets name.

Thanks again,
~jaeson