Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Work book event code help (re Bob Phillips)
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Work book event code help (re Bob Phillips)
On Nov 8, 11:08 am, Dingy101
wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
work book open _ event | Excel Worksheet Functions | |||
App_change event to open all work book | Excel Discussion (Misc queries) | |||
searching by number or code ina work book | Excel Discussion (Misc queries) | |||
Code to protect sheets, protects work book also? | Excel Programming | |||
Please Help!!! How Do I Specify A particualr work book to execute code on | Excel Programming |