sheet names displayed in first workbook sheet help

[Dingy101]

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

 

[Gary''s Student]

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.

 

[Dingy101]

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]

Sub listnames()
Range("C27").Select
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

You can assign a shortcut key with:
Tools > Macros... > Macro > Options

The click to start is only a little more complex.

 

[Bob Phillips]

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]

This works if the target address cell is not merged with other cells. Due to
the layout of my worksheet, the target address is a merged cell E31:J31. If I
unmerge this cell the list works fine with the exception of the first sheet
name. The first name is displayed in the cell at E31 with the remainder of
the names displaying as desired in the still merged cells from E32:J32 thru
E45:J45. Is there a way to make this work with this merged cell? If not, can
I designate another cell as the cell that initiates the event , say (E28)
with the list starting in the first merged cell at E31:J31 ?. I have a
maximum of 15 sheets in the workbook, so the list extends from the merged
cell at E31:J31 to a max of E45:J45.



Thanks for the help getting me this far !!

 

[Bob Phillips]

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

If Not Intersect(Target, Me.Range("$C$27")) Is Nothing 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



--
HTH

Bob

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

 

[Dingy101]

Thanks for the help in getting this to work !!!

Gary Dinges

 

[Dingy101]

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

 

[joel]

Sub GetSheetNames()

set NewBk = workbooks("Book1.xls")

with NewBk
RowCount = 1
for each sht in .sheets
if ucase(sht.name) <> ucase("A") then
Sheets("A").Range("A" & RowCount) = sht.name
RowCount = RowCount + 1
end if
next Sht
end with

end Su