Work book event code help (re Bob Phillips)

[Dingy101]

I have a worksheet that has numerous sheets in it that the code shown below
which was developed by Bob Phillips when run lists all the remaining shees in
a range of cells in the first sheet in the workbook (which is named
'Operations'). What I would like to do with this code is to modify it so it
skips 2 sheets that may or may not still be in the copied workbook. These 2
sheets are named 'Strippit Radan' & 'Burner Radan'. Would someone please
help me with this?

I have listed the code below & below that I copied my original request for
background info.


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

If Target.Address = "$E$25" Then
n = Worksheets.Count
j = 0
For i = 1 To n
sh = Worksheets(i).Name
If sh <> "blank" Then
Target.Offset(j, 0).Value = sh
j = j + 1
End If
Next
End If
End Sub


Original request

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 workbooks. 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

 

[carlo]

I have a worksheet that has numerous sheets in it that the code shown below
which was developed by Bob Phillips when run lists all the remaining shees in
a range of cells in the first sheet in the workbook (which is named
'Operations'). What I would like to do with this code is to modify it so it
skips 2 sheets that may or may not still be in the copied workbook. These 2
sheets are named 'Strippit Radan' & 'Burner Radan'. Would someone please
help me with this?

I have listed the code below & below that I copied my original request for
background info.

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

If Target.Address = "$E$25" Then
n = Worksheets.Count
j = 0
For i = 1 To n
sh = Worksheets(i).Name
If sh <> "blank" Then
Target.Offset(j, 0).Value = sh
j = j + 1
End If
Next
End If
End Sub

Original request

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 workbooks. 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

Hi Gary

that should work:

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


If Target.Address = "$E$25" Then
n = Worksheets.Count
j = 0
For i = 1 To n
sh = Worksheets(i).Name
If sh <> "blank" and sh <> "Strippit Radan" and sh <>
"Burner Radan" Then
Target.Offset(j, 0).Value = sh
j = j + 1
End If
Next
End If
End Sub


hth Carlo