Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim n As Long
Dim i As Long
Dim j As Long

If Target.Address = "$C$27" Then
n = Worksheets.Count
j = 0
For i = 1 To n
sh = Worksheets(i).Name
If sh < "Sheet A" Then
Target.Offset(j, 0).Value = sh
j = j + 1
End If
Next
End If
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Dingy101" wrote in message
...
This works great!!!

What would I change in the code to have the list always start a
predetermined cell rather than the active cell ?

Say cell C27

And one more question, can I assign this macro to a keystroke or function
key?? Or better yet if I click on a cell in the work sheet to have the
macro
run?

Thank You !!!

Gary Dinges




"Gary''s Student" wrote:

Click any cell and run this macro:

Sub listnames()
n = Worksheets.Count
For i = 1 To n
sh = Worksheets(i).Name
If sh = "Sheet A" Then
Else
ActiveCell.Value = sh
ActiveCell.Offset(1, 0).Select
End If
Next
End Sub

It will produce a list of worksheet names starting from the cell you
selected.
--
Gary''s Student - gsnu200752


"Dingy101" wrote:

I have an Excel 2003 workbook that has 12 sheets in it. This workbook
is the
master copy that is copied into each one of our job folders as a new
job is
created. The first sheet is always kept in the workbook, but the
remainder of
the sheets are not always kept in the copied woorbooks. The jobs vary
in what
work centers in our shop are required for each job. If a work center is
not
required for a specific job, I delete that work centers sheet from the
copied
wook book. For simplicity, assuming that the sheets are named A, B,
C,....
and that sheet A is always kept in the copied wookbook., is there a way
that
I can get the remaining sheet names to be displayed in cells in sheet
A.

What I would like to accomplish is a sequential block of cells that
display
the remaining sheets names with no gaps between the cells with the
displayed
names in them.

Can someone help me with this please??

Thank You,

Gary