Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheets names
Hi Guys,
I need a VBA to sort all the sheet's names in a column, Can this list be updated automatically In case I delete any sheets? Can anyone please direct me to create such VBA? Thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheets names
HI
Sub listSheets() s = Sheets.Count r = ActiveCell.Row: c = ActiveCell.Column For i = 1 To s Cells(r, c) = Sheets(i).Name Cells(r, c + 1) = Sheets(i).Index r = r + 1 Next End Sub Regards Peter "2007-User" wrote: Hi Guys, I need a VBA to sort all the sheet's names in a column, Can this list be updated automatically In case I delete any sheets? Can anyone please direct me to create such VBA? Thanks in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheets names
Thank you so much Peter
How can I make it auto_refreshable in case I add/removed or renamed any sheets? Thanks again. "PeterAtherton" wrote in message ... HI Sub listSheets() s = Sheets.Count r = ActiveCell.Row: c = ActiveCell.Column For i = 1 To s Cells(r, c) = Sheets(i).Name Cells(r, c + 1) = Sheets(i).Index r = r + 1 Next End Sub Regards Peter "2007-User" wrote: Hi Guys, I need a VBA to sort all the sheet's names in a column, Can this list be updated automatically In case I delete any sheets? Can anyone please direct me to create such VBA? Thanks in advance. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheets names
Hi
Right-click on the Excel icon at the left of the file toolbar and choose View Code. This opens the Workbook code sheet. Copy the revised formula into the code. Private Sub workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean) 'sheets("Menu").select 'cells(4,2).select s = Sheets.Count r = ActiveCell.Row: c = ActiveCell.Column For i = 1 To s Cells(r, c) = Sheets(i).Name Cells(r, c + 1) = Sheets(i).Index r = r + 1 Next End Sub Beware the code write the sheets every time you save the workbook. in this case I think you should insert lines to place the list on a particular sheet and lines. I've added a couple of lines to the code to give you the idea; just remove the rems (apostrophes) if they are suitable Regards Peter "2007-User" wrote: Thank you so much Peter How can I make it auto_refreshable in case I add/removed or renamed any sheets? Thanks again. "PeterAtherton" wrote in message ... HI Sub listSheets() s = Sheets.Count r = ActiveCell.Row: c = ActiveCell.Column For i = 1 To s Cells(r, c) = Sheets(i).Name Cells(r, c + 1) = Sheets(i).Index r = r + 1 Next End Sub Regards Peter "2007-User" wrote: Hi Guys, I need a VBA to sort all the sheet's names in a column, Can this list be updated automatically In case I delete any sheets? Can anyone please direct me to create such VBA? Thanks in advance. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheets names
I have not woken up yet. You might delete sheets so you will need to clear
the original list. The rest is OK - it still goes in the workbook module. Private Sub workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean) Sheets("Menu").Select Range("B2").Select ' you might delete sheets so the updated ' list will be smaller Range("B2").CurrentRegion.ClearContents s = Sheets.Count r = ActiveCell.Row: c = ActiveCell.Column For i = 1 To s Cells(r, c) = Sheets(i).Name Cells(r, c + 1) = Sheets(i).Index r = r + 1 Next End Sub regards "PeterAtherton" wrote: Hi Right-click on the Excel icon at the left of the file toolbar and choose View Code. This opens the Workbook code sheet. Copy the revised formula into the code. Private Sub workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean) 'sheets("Menu").select 'cells(4,2).select s = Sheets.Count r = ActiveCell.Row: c = ActiveCell.Column For i = 1 To s Cells(r, c) = Sheets(i).Name Cells(r, c + 1) = Sheets(i).Index r = r + 1 Next End Sub Beware the code write the sheets every time you save the workbook. in this case I think you should insert lines to place the list on a particular sheet and lines. I've added a couple of lines to the code to give you the idea; just remove the rems (apostrophes) if they are suitable Regards Peter "2007-User" wrote: Thank you so much Peter How can I make it auto_refreshable in case I add/removed or renamed any sheets? Thanks again. "PeterAtherton" wrote in message ... HI Sub listSheets() s = Sheets.Count r = ActiveCell.Row: c = ActiveCell.Column For i = 1 To s Cells(r, c) = Sheets(i).Name Cells(r, c + 1) = Sheets(i).Index r = r + 1 Next End Sub Regards Peter "2007-User" wrote: Hi Guys, I need a VBA to sort all the sheet's names in a column, Can this list be updated automatically In case I delete any sheets? Can anyone please direct me to create such VBA? Thanks in advance. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheets names
Thanks so much Peter you help me a lot.
"PeterAtherton" wrote in message ... I have not woken up yet. You might delete sheets so you will need to clear the original list. The rest is OK - it still goes in the workbook module. Private Sub workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean) Sheets("Menu").Select Range("B2").Select ' you might delete sheets so the updated ' list will be smaller Range("B2").CurrentRegion.ClearContents s = Sheets.Count r = ActiveCell.Row: c = ActiveCell.Column For i = 1 To s Cells(r, c) = Sheets(i).Name Cells(r, c + 1) = Sheets(i).Index r = r + 1 Next End Sub regards "PeterAtherton" wrote: Hi Right-click on the Excel icon at the left of the file toolbar and choose View Code. This opens the Workbook code sheet. Copy the revised formula into the code. Private Sub workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean) 'sheets("Menu").select 'cells(4,2).select s = Sheets.Count r = ActiveCell.Row: c = ActiveCell.Column For i = 1 To s Cells(r, c) = Sheets(i).Name Cells(r, c + 1) = Sheets(i).Index r = r + 1 Next End Sub Beware the code write the sheets every time you save the workbook. in this case I think you should insert lines to place the list on a particular sheet and lines. I've added a couple of lines to the code to give you the idea; just remove the rems (apostrophes) if they are suitable Regards Peter "2007-User" wrote: Thank you so much Peter How can I make it auto_refreshable in case I add/removed or renamed any sheets? Thanks again. "PeterAtherton" wrote in message ... HI Sub listSheets() s = Sheets.Count r = ActiveCell.Row: c = ActiveCell.Column For i = 1 To s Cells(r, c) = Sheets(i).Name Cells(r, c + 1) = Sheets(i).Index r = r + 1 Next End Sub Regards Peter "2007-User" wrote: Hi Guys, I need a VBA to sort all the sheet's names in a column, Can this list be updated automatically In case I delete any sheets? Can anyone please direct me to create such VBA? Thanks in advance. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheets names
Peter,
1- Can I call this list_refresher when I am switching between the sheets? 2- I have a little problem, when I am in other sheets, when I push the save bottom the list create itself on that sheet also, is there anyway to lead the program to run itself just on that specific sheet? Thanks again. "PeterAtherton" wrote in message ... I have not woken up yet. You might delete sheets so you will need to clear the original list. The rest is OK - it still goes in the workbook module. Private Sub workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean) Sheets("Menu").Select Range("B2").Select ' you might delete sheets so the updated ' list will be smaller Range("B2").CurrentRegion.ClearContents s = Sheets.Count r = ActiveCell.Row: c = ActiveCell.Column For i = 1 To s Cells(r, c) = Sheets(i).Name Cells(r, c + 1) = Sheets(i).Index r = r + 1 Next End Sub regards "PeterAtherton" wrote: Hi Right-click on the Excel icon at the left of the file toolbar and choose View Code. This opens the Workbook code sheet. Copy the revised formula into the code. Private Sub workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean) 'sheets("Menu").select 'cells(4,2).select s = Sheets.Count r = ActiveCell.Row: c = ActiveCell.Column For i = 1 To s Cells(r, c) = Sheets(i).Name Cells(r, c + 1) = Sheets(i).Index r = r + 1 Next End Sub Beware the code write the sheets every time you save the workbook. in this case I think you should insert lines to place the list on a particular sheet and lines. I've added a couple of lines to the code to give you the idea; just remove the rems (apostrophes) if they are suitable Regards Peter "2007-User" wrote: Thank you so much Peter How can I make it auto_refreshable in case I add/removed or renamed any sheets? Thanks again. "PeterAtherton" wrote in message ... HI Sub listSheets() s = Sheets.Count r = ActiveCell.Row: c = ActiveCell.Column For i = 1 To s Cells(r, c) = Sheets(i).Name Cells(r, c + 1) = Sheets(i).Index r = r + 1 Next End Sub Regards Peter "2007-User" wrote: Hi Guys, I need a VBA to sort all the sheet's names in a column, Can this list be updated automatically In case I delete any sheets? Can anyone please direct me to create such VBA? Thanks in advance. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheets names
I found a solution to solve my 2nd problem which is using activesheet code,
please see below: If ActiveSheet.Name = "COVER SHEET" Then listSheets 'call listsheets module "2007-User" wrote in message ... Peter, 1- Can I call this list_refresher when I am switching between the sheets? 2- I have a little problem, when I am in other sheets, when I push the save bottom the list create itself on that sheet also, is there anyway to lead the program to run itself just on that specific sheet? Thanks again. "PeterAtherton" wrote in message ... I have not woken up yet. You might delete sheets so you will need to clear the original list. The rest is OK - it still goes in the workbook module. Private Sub workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean) Sheets("Menu").Select Range("B2").Select ' you might delete sheets so the updated ' list will be smaller Range("B2").CurrentRegion.ClearContents s = Sheets.Count r = ActiveCell.Row: c = ActiveCell.Column For i = 1 To s Cells(r, c) = Sheets(i).Name Cells(r, c + 1) = Sheets(i).Index r = r + 1 Next End Sub regards "PeterAtherton" wrote: Hi Right-click on the Excel icon at the left of the file toolbar and choose View Code. This opens the Workbook code sheet. Copy the revised formula into the code. Private Sub workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean) 'sheets("Menu").select 'cells(4,2).select s = Sheets.Count r = ActiveCell.Row: c = ActiveCell.Column For i = 1 To s Cells(r, c) = Sheets(i).Name Cells(r, c + 1) = Sheets(i).Index r = r + 1 Next End Sub Beware the code write the sheets every time you save the workbook. in this case I think you should insert lines to place the list on a particular sheet and lines. I've added a couple of lines to the code to give you the idea; just remove the rems (apostrophes) if they are suitable Regards Peter "2007-User" wrote: Thank you so much Peter How can I make it auto_refreshable in case I add/removed or renamed any sheets? Thanks again. "PeterAtherton" wrote in message ... HI Sub listSheets() s = Sheets.Count r = ActiveCell.Row: c = ActiveCell.Column For i = 1 To s Cells(r, c) = Sheets(i).Name Cells(r, c + 1) = Sheets(i).Index r = r + 1 Next End Sub Regards Peter "2007-User" wrote: Hi Guys, I need a VBA to sort all the sheet's names in a column, Can this list be updated automatically In case I delete any sheets? Can anyone please direct me to create such VBA? Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sheets Names in Formulas | Excel Discussion (Misc queries) | |||
Sheets names | Excel Discussion (Misc queries) | |||
Cell Names in Two Sheets | Excel Discussion (Misc queries) | |||
cells and sheets, names | Excel Worksheet Functions | |||
how do i get the names of the sheets | Excel Worksheet Functions |