Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
sheet names displayed in first workbook sheet help
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
sheet names displayed in first workbook sheet help
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
sheet names displayed in first workbook sheet help
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
sheet names displayed in first workbook sheet help
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. -- Gary''s Student - gsnu200752 "Dingy101" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
sheet names displayed in first workbook sheet help
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
sheet names displayed in first workbook sheet help
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" wrote: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim n As Long Dim i As Long Dim j As Long If Target.Address = "$E$31" 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 E31 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
sheet names displayed in first workbook sheet help
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" wrote in message ... 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" wrote: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim n As Long Dim i As Long Dim j As Long If Target.Address = "$E$31" 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 E31 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
sheet names displayed in first workbook sheet help
Thanks for the help in getting this to work !!!
Gary Dinges "Bob Phillips" wrote: 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" wrote in message ... 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" wrote: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim n As Long Dim i As Long Dim j As Long If Target.Address = "$E$31" 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 E31 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
sheet names displayed in first workbook sheet help
"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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
sheet names displayed in first workbook sheet help
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 Sub -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=159804 Microsoft Office Help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there an object containing the sheet names for a workbook | Excel Programming | |||
Iterating Through Workbook if Don't Know Sheet Names | Excel Programming | |||
View All Sheet Names in Workbook at Once | Excel Discussion (Misc queries) | |||
Using Sheet names & Workbook names in VBA coding | Excel Programming | |||
Retrieving the sheet names of another workbook | Excel Programming |