|
how can I make a table of worksheet names that updates automatical
I have a file with 20+ worksheets that keeps increasing in size. They are
not, unfortunately, in any alphabetical order. I'd like a table of contents page that I can sort by alpha, if necessary, that will update itself as new pages are added. I am using Excel 2003. |
how can I make a table of worksheet names that updates automatical
Copy and paste this code into the _Activate() event for the worksheet that
you want to be the Table of Contents sheet: Private Sub Worksheet_Activate() Dim anySheet As Worksheet Dim myCounter As Long Cells.Clear ' remove previous content myCounter = 0 For Each anySheet In Worksheets If anySheet.Name < ActiveSheet.Name Then Range("A1").Offset(myCounter, 0) = anySheet.Name 'this adds hyperlink remove if you don't want it ActiveSheet.Hyperlinks.Add anchor:=Range("A1").Offset(myCounter, 0), _ Address:="", SubAddress:=anySheet.Name & "!A1" 'update the offset counter, keep this myCounter = myCounter + 1 End If Next End Sub I took the liberty of setting up a hyperlink in each one to take you right to that sheet if you click on one of them. Instructions on this page will show you how to get to the place you need to be to add the code to the Worksheet events section. http://www.jlathamsite.com/Teach/WorksheetCode.htm Each time you choose this worksheet the list will be updated to reflect all current sheets in the workbook except itself. Since you didn't ask for automatic sorting, it's not there. To use normal sort on the list, it's kind of tricky with cells with hyperlinks: click in one of the cells (top or bottom) and hold the mousebutton down for a short time and then drag to include the whole list and sort. You have to click and hold to select such a cell instead of just clicking, which activates the hyperlink immediately. If you don't want the hyperlinks, just leave out that one instruction (on 2 lines) and no hyperlink will be created. "nah" wrote: I have a file with 20+ worksheets that keeps increasing in size. They are not, unfortunately, in any alphabetical order. I'd like a table of contents page that I can sort by alpha, if necessary, that will update itself as new pages are added. I am using Excel 2003. |
how can I make a table of worksheet names that updates automat
thanks. I get the list, but the links do not work. "reference not valid". THe
hyperlink is not necessary, but would be nice. I can sort the list by selecting the column and sorting it. "JLatham" wrote: Copy and paste this code into the _Activate() event for the worksheet that you want to be the Table of Contents sheet: Private Sub Worksheet_Activate() Dim anySheet As Worksheet Dim myCounter As Long Cells.Clear ' remove previous content myCounter = 0 For Each anySheet In Worksheets If anySheet.Name < ActiveSheet.Name Then Range("A1").Offset(myCounter, 0) = anySheet.Name 'this adds hyperlink remove if you don't want it ActiveSheet.Hyperlinks.Add anchor:=Range("A1").Offset(myCounter, 0), _ Address:="", SubAddress:=anySheet.Name & "!A1" 'update the offset counter, keep this myCounter = myCounter + 1 End If Next End Sub I took the liberty of setting up a hyperlink in each one to take you right to that sheet if you click on one of them. Instructions on this page will show you how to get to the place you need to be to add the code to the Worksheet events section. http://www.jlathamsite.com/Teach/WorksheetCode.htm Each time you choose this worksheet the list will be updated to reflect all current sheets in the workbook except itself. Since you didn't ask for automatic sorting, it's not there. To use normal sort on the list, it's kind of tricky with cells with hyperlinks: click in one of the cells (top or bottom) and hold the mousebutton down for a short time and then drag to include the whole list and sort. You have to click and hold to select such a cell instead of just clicking, which activates the hyperlink immediately. If you don't want the hyperlinks, just leave out that one instruction (on 2 lines) and no hyperlink will be created. "nah" wrote: I have a file with 20+ worksheets that keeps increasing in size. They are not, unfortunately, in any alphabetical order. I'd like a table of contents page that I can sort by alpha, if necessary, that will update itself as new pages are added. I am using Excel 2003. |
how can I make a table of worksheet names that updates automat
I'll look and try to see what might be wrong with the hyperlink setup. Could
be that some of your sheets have a space in their names? "nah" wrote: thanks. I get the list, but the links do not work. "reference not valid". THe hyperlink is not necessary, but would be nice. I can sort the list by selecting the column and sorting it. "JLatham" wrote: Copy and paste this code into the _Activate() event for the worksheet that you want to be the Table of Contents sheet: Private Sub Worksheet_Activate() Dim anySheet As Worksheet Dim myCounter As Long Cells.Clear ' remove previous content myCounter = 0 For Each anySheet In Worksheets If anySheet.Name < ActiveSheet.Name Then Range("A1").Offset(myCounter, 0) = anySheet.Name 'this adds hyperlink remove if you don't want it ActiveSheet.Hyperlinks.Add anchor:=Range("A1").Offset(myCounter, 0), _ Address:="", SubAddress:=anySheet.Name & "!A1" 'update the offset counter, keep this myCounter = myCounter + 1 End If Next End Sub I took the liberty of setting up a hyperlink in each one to take you right to that sheet if you click on one of them. Instructions on this page will show you how to get to the place you need to be to add the code to the Worksheet events section. http://www.jlathamsite.com/Teach/WorksheetCode.htm Each time you choose this worksheet the list will be updated to reflect all current sheets in the workbook except itself. Since you didn't ask for automatic sorting, it's not there. To use normal sort on the list, it's kind of tricky with cells with hyperlinks: click in one of the cells (top or bottom) and hold the mousebutton down for a short time and then drag to include the whole list and sort. You have to click and hold to select such a cell instead of just clicking, which activates the hyperlink immediately. If you don't want the hyperlinks, just leave out that one instruction (on 2 lines) and no hyperlink will be created. "nah" wrote: I have a file with 20+ worksheets that keeps increasing in size. They are not, unfortunately, in any alphabetical order. I'd like a table of contents page that I can sort by alpha, if necessary, that will update itself as new pages are added. I am using Excel 2003. |
how can I make a table of worksheet names that updates automat
Make sure your sheet names don't contain the # symbol, that'll mess up
hyperlinks also. Most likely problem right now is that there's a space in one or more sheet names, this would fix that, just replace the SubAddress:= portion with this: SubAddress:="'" & anySheet.Name & "'!A1" if that's hard to read here, that is double-quote, single-quote, double-quote in the first piece, then I added a single-quote in front of the exclamation point in the trailing portion, so as to creat an entry similar to this 'Sheet Name'!A1 "nah" wrote: thanks. I get the list, but the links do not work. "reference not valid". THe hyperlink is not necessary, but would be nice. I can sort the list by selecting the column and sorting it. "JLatham" wrote: Copy and paste this code into the _Activate() event for the worksheet that you want to be the Table of Contents sheet: Private Sub Worksheet_Activate() Dim anySheet As Worksheet Dim myCounter As Long Cells.Clear ' remove previous content myCounter = 0 For Each anySheet In Worksheets If anySheet.Name < ActiveSheet.Name Then Range("A1").Offset(myCounter, 0) = anySheet.Name 'this adds hyperlink remove if you don't want it ActiveSheet.Hyperlinks.Add anchor:=Range("A1").Offset(myCounter, 0), _ Address:="", SubAddress:=anySheet.Name & "!A1" 'update the offset counter, keep this myCounter = myCounter + 1 End If Next End Sub I took the liberty of setting up a hyperlink in each one to take you right to that sheet if you click on one of them. Instructions on this page will show you how to get to the place you need to be to add the code to the Worksheet events section. http://www.jlathamsite.com/Teach/WorksheetCode.htm Each time you choose this worksheet the list will be updated to reflect all current sheets in the workbook except itself. Since you didn't ask for automatic sorting, it's not there. To use normal sort on the list, it's kind of tricky with cells with hyperlinks: click in one of the cells (top or bottom) and hold the mousebutton down for a short time and then drag to include the whole list and sort. You have to click and hold to select such a cell instead of just clicking, which activates the hyperlink immediately. If you don't want the hyperlinks, just leave out that one instruction (on 2 lines) and no hyperlink will be created. "nah" wrote: I have a file with 20+ worksheets that keeps increasing in size. They are not, unfortunately, in any alphabetical order. I'd like a table of contents page that I can sort by alpha, if necessary, that will update itself as new pages are added. I am using Excel 2003. |
how can I make a table of worksheet names that updates automat
Thanks. I probably should have thought about the spaces, we have come up
against that problem with hyperlinks in the past on stuff I have tested, but I wasnt' thinking. At first it wasn't working, but for some reason now it is. I can still sort by the column, although I need to re-do it each time. But that's fine, it is still much faster than searching manually through 46 (at this point) sheets blindly to see if I have a particular one or not. "JLatham" wrote: Make sure your sheet names don't contain the # symbol, that'll mess up hyperlinks also. Most likely problem right now is that there's a space in one or more sheet names, this would fix that, just replace the SubAddress:= portion with this: SubAddress:="'" & anySheet.Name & "'!A1" if that's hard to read here, that is double-quote, single-quote, double-quote in the first piece, then I added a single-quote in front of the exclamation point in the trailing portion, so as to creat an entry similar to this 'Sheet Name'!A1 "nah" wrote: thanks. I get the list, but the links do not work. "reference not valid". THe hyperlink is not necessary, but would be nice. I can sort the list by selecting the column and sorting it. "JLatham" wrote: Copy and paste this code into the _Activate() event for the worksheet that you want to be the Table of Contents sheet: Private Sub Worksheet_Activate() Dim anySheet As Worksheet Dim myCounter As Long Cells.Clear ' remove previous content myCounter = 0 For Each anySheet In Worksheets If anySheet.Name < ActiveSheet.Name Then Range("A1").Offset(myCounter, 0) = anySheet.Name 'this adds hyperlink remove if you don't want it ActiveSheet.Hyperlinks.Add anchor:=Range("A1").Offset(myCounter, 0), _ Address:="", SubAddress:=anySheet.Name & "!A1" 'update the offset counter, keep this myCounter = myCounter + 1 End If Next End Sub I took the liberty of setting up a hyperlink in each one to take you right to that sheet if you click on one of them. Instructions on this page will show you how to get to the place you need to be to add the code to the Worksheet events section. http://www.jlathamsite.com/Teach/WorksheetCode.htm Each time you choose this worksheet the list will be updated to reflect all current sheets in the workbook except itself. Since you didn't ask for automatic sorting, it's not there. To use normal sort on the list, it's kind of tricky with cells with hyperlinks: click in one of the cells (top or bottom) and hold the mousebutton down for a short time and then drag to include the whole list and sort. You have to click and hold to select such a cell instead of just clicking, which activates the hyperlink immediately. If you don't want the hyperlinks, just leave out that one instruction (on 2 lines) and no hyperlink will be created. "nah" wrote: I have a file with 20+ worksheets that keeps increasing in size. They are not, unfortunately, in any alphabetical order. I'd like a table of contents page that I can sort by alpha, if necessary, that will update itself as new pages are added. I am using Excel 2003. |
how can I make a table of worksheet names that updates automat
Hello,
I've just used this code and it works great! thank you! I just have one question though. Is it exclusively for data sheets or will it pick up chart sheets? I have a huge workbook of pivots and pivot charts, this code works on the pivot and data sheets, however, it's not picking up the pivot charts worksheets. could you help please? Many thanks and much appreciated TL "JLatham" wrote: Make sure your sheet names don't contain the # symbol, that'll mess up hyperlinks also. Most likely problem right now is that there's a space in one or more sheet names, this would fix that, just replace the SubAddress:= portion with this: SubAddress:="'" & anySheet.Name & "'!A1" if that's hard to read here, that is double-quote, single-quote, double-quote in the first piece, then I added a single-quote in front of the exclamation point in the trailing portion, so as to creat an entry similar to this 'Sheet Name'!A1 "nah" wrote: thanks. I get the list, but the links do not work. "reference not valid". THe hyperlink is not necessary, but would be nice. I can sort the list by selecting the column and sorting it. "JLatham" wrote: Copy and paste this code into the _Activate() event for the worksheet that you want to be the Table of Contents sheet: Private Sub Worksheet_Activate() Dim anySheet As Worksheet Dim myCounter As Long Cells.Clear ' remove previous content myCounter = 0 For Each anySheet In Worksheets If anySheet.Name < ActiveSheet.Name Then Range("A1").Offset(myCounter, 0) = anySheet.Name 'this adds hyperlink remove if you don't want it ActiveSheet.Hyperlinks.Add anchor:=Range("A1").Offset(myCounter, 0), _ Address:="", SubAddress:=anySheet.Name & "!A1" 'update the offset counter, keep this myCounter = myCounter + 1 End If Next End Sub I took the liberty of setting up a hyperlink in each one to take you right to that sheet if you click on one of them. Instructions on this page will show you how to get to the place you need to be to add the code to the Worksheet events section. http://www.jlathamsite.com/Teach/WorksheetCode.htm Each time you choose this worksheet the list will be updated to reflect all current sheets in the workbook except itself. Since you didn't ask for automatic sorting, it's not there. To use normal sort on the list, it's kind of tricky with cells with hyperlinks: click in one of the cells (top or bottom) and hold the mousebutton down for a short time and then drag to include the whole list and sort. You have to click and hold to select such a cell instead of just clicking, which activates the hyperlink immediately. If you don't want the hyperlinks, just leave out that one instruction (on 2 lines) and no hyperlink will be created. "nah" wrote: I have a file with 20+ worksheets that keeps increasing in size. They are not, unfortunately, in any alphabetical order. I'd like a table of contents page that I can sort by alpha, if necessary, that will update itself as new pages are added. I am using Excel 2003. |
how can I make a table of worksheet names that updates automat
Try this macro.
Private Sub ListSheets() 'list of sheet names starting at A1 Dim Rng As Range Dim i As Integer Set Rng = Range("A1") For Each Sheet In ActiveWorkbook.Sheets Rng.Offset(i, 0).Value = Sheet.Name i = i + 1 Next Sheet End Sub Gord Dibben MS Excel MVP On Wed, 6 Dec 2006 09:36:00 -0800, RedDevil wrote: Hello, I've just used this code and it works great! thank you! I just have one question though. Is it exclusively for data sheets or will it pick up chart sheets? I have a huge workbook of pivots and pivot charts, this code works on the pivot and data sheets, however, it's not picking up the pivot charts worksheets. could you help please? Many thanks and much appreciated TL "JLatham" wrote: Make sure your sheet names don't contain the # symbol, that'll mess up hyperlinks also. Most likely problem right now is that there's a space in one or more sheet names, this would fix that, just replace the SubAddress:= portion with this: SubAddress:="'" & anySheet.Name & "'!A1" if that's hard to read here, that is double-quote, single-quote, double-quote in the first piece, then I added a single-quote in front of the exclamation point in the trailing portion, so as to creat an entry similar to this 'Sheet Name'!A1 "nah" wrote: thanks. I get the list, but the links do not work. "reference not valid". THe hyperlink is not necessary, but would be nice. I can sort the list by selecting the column and sorting it. "JLatham" wrote: Copy and paste this code into the _Activate() event for the worksheet that you want to be the Table of Contents sheet: Private Sub Worksheet_Activate() Dim anySheet As Worksheet Dim myCounter As Long Cells.Clear ' remove previous content myCounter = 0 For Each anySheet In Worksheets If anySheet.Name < ActiveSheet.Name Then Range("A1").Offset(myCounter, 0) = anySheet.Name 'this adds hyperlink remove if you don't want it ActiveSheet.Hyperlinks.Add anchor:=Range("A1").Offset(myCounter, 0), _ Address:="", SubAddress:=anySheet.Name & "!A1" 'update the offset counter, keep this myCounter = myCounter + 1 End If Next End Sub I took the liberty of setting up a hyperlink in each one to take you right to that sheet if you click on one of them. Instructions on this page will show you how to get to the place you need to be to add the code to the Worksheet events section. http://www.jlathamsite.com/Teach/WorksheetCode.htm Each time you choose this worksheet the list will be updated to reflect all current sheets in the workbook except itself. Since you didn't ask for automatic sorting, it's not there. To use normal sort on the list, it's kind of tricky with cells with hyperlinks: click in one of the cells (top or bottom) and hold the mousebutton down for a short time and then drag to include the whole list and sort. You have to click and hold to select such a cell instead of just clicking, which activates the hyperlink immediately. If you don't want the hyperlinks, just leave out that one instruction (on 2 lines) and no hyperlink will be created. "nah" wrote: I have a file with 20+ worksheets that keeps increasing in size. They are not, unfortunately, in any alphabetical order. I'd like a table of contents page that I can sort by alpha, if necessary, that will update itself as new pages are added. I am using Excel 2003. |
how can I make a table of worksheet names that updates automat
This works fantastic! Thanks! Is there a way to change "Cells.Clear" so
that it only clears a certain section instead of the whole sheet? We used abbreviations for our sheet names but would like to have the longer name next to the abbreviated hyperlink for people new to the abreviations. I have modified it so that the hyperlinks appera in Column B, starting in cell 3, can we have it clear only column B from 3+? Thanks in advance. "JLatham" wrote: Make sure your sheet names don't contain the # symbol, that'll mess up hyperlinks also. Most likely problem right now is that there's a space in one or more sheet names, this would fix that, just replace the SubAddress:= portion with this: SubAddress:="'" & anySheet.Name & "'!A1" if that's hard to read here, that is double-quote, single-quote, double-quote in the first piece, then I added a single-quote in front of the exclamation point in the trailing portion, so as to creat an entry similar to this 'Sheet Name'!A1 "nah" wrote: thanks. I get the list, but the links do not work. "reference not valid". THe hyperlink is not necessary, but would be nice. I can sort the list by selecting the column and sorting it. "JLatham" wrote: Copy and paste this code into the _Activate() event for the worksheet that you want to be the Table of Contents sheet: Private Sub Worksheet_Activate() Dim anySheet As Worksheet Dim myCounter As Long Cells.Clear ' remove previous content myCounter = 0 For Each anySheet In Worksheets If anySheet.Name < ActiveSheet.Name Then Range("A1").Offset(myCounter, 0) = anySheet.Name 'this adds hyperlink remove if you don't want it ActiveSheet.Hyperlinks.Add anchor:=Range("A1").Offset(myCounter, 0), _ Address:="", SubAddress:=anySheet.Name & "!A1" 'update the offset counter, keep this myCounter = myCounter + 1 End If Next End Sub I took the liberty of setting up a hyperlink in each one to take you right to that sheet if you click on one of them. Instructions on this page will show you how to get to the place you need to be to add the code to the Worksheet events section. http://www.jlathamsite.com/Teach/WorksheetCode.htm Each time you choose this worksheet the list will be updated to reflect all current sheets in the workbook except itself. Since you didn't ask for automatic sorting, it's not there. To use normal sort on the list, it's kind of tricky with cells with hyperlinks: click in one of the cells (top or bottom) and hold the mousebutton down for a short time and then drag to include the whole list and sort. You have to click and hold to select such a cell instead of just clicking, which activates the hyperlink immediately. If you don't want the hyperlinks, just leave out that one instruction (on 2 lines) and no hyperlink will be created. "nah" wrote: I have a file with 20+ worksheets that keeps increasing in size. They are not, unfortunately, in any alphabetical order. I'd like a table of contents page that I can sort by alpha, if necessary, that will update itself as new pages are added. I am using Excel 2003. |
how can I make a table of worksheet names that updates automat
I'm another fan of your helpfulness and code. Thought I had it, but it will
not pull in more than 41 sheets. Any ideas. -- bai_AK_apo "JLatham" wrote: Make sure your sheet names don't contain the # symbol, that'll mess up hyperlinks also. Most likely problem right now is that there's a space in one or more sheet names, this would fix that, just replace the SubAddress:= portion with this: SubAddress:="'" & anySheet.Name & "'!A1" if that's hard to read here, that is double-quote, single-quote, double-quote in the first piece, then I added a single-quote in front of the exclamation point in the trailing portion, so as to creat an entry similar to this 'Sheet Name'!A1 "nah" wrote: thanks. I get the list, but the links do not work. "reference not valid". THe hyperlink is not necessary, but would be nice. I can sort the list by selecting the column and sorting it. "JLatham" wrote: Copy and paste this code into the _Activate() event for the worksheet that you want to be the Table of Contents sheet: Private Sub Worksheet_Activate() Dim anySheet As Worksheet Dim myCounter As Long Cells.Clear ' remove previous content myCounter = 0 For Each anySheet In Worksheets If anySheet.Name < ActiveSheet.Name Then Range("A1").Offset(myCounter, 0) = anySheet.Name 'this adds hyperlink remove if you don't want it ActiveSheet.Hyperlinks.Add anchor:=Range("A1").Offset(myCounter, 0), _ Address:="", SubAddress:=anySheet.Name & "!A1" 'update the offset counter, keep this myCounter = myCounter + 1 End If Next End Sub I took the liberty of setting up a hyperlink in each one to take you right to that sheet if you click on one of them. Instructions on this page will show you how to get to the place you need to be to add the code to the Worksheet events section. http://www.jlathamsite.com/Teach/WorksheetCode.htm Each time you choose this worksheet the list will be updated to reflect all current sheets in the workbook except itself. Since you didn't ask for automatic sorting, it's not there. To use normal sort on the list, it's kind of tricky with cells with hyperlinks: click in one of the cells (top or bottom) and hold the mousebutton down for a short time and then drag to include the whole list and sort. You have to click and hold to select such a cell instead of just clicking, which activates the hyperlink immediately. If you don't want the hyperlinks, just leave out that one instruction (on 2 lines) and no hyperlink will be created. "nah" wrote: I have a file with 20+ worksheets that keeps increasing in size. They are not, unfortunately, in any alphabetical order. I'd like a table of contents page that I can sort by alpha, if necessary, that will update itself as new pages are added. I am using Excel 2003. |
how can I make a table of worksheet names that updates automat
I am using this macro, but how can i add a step so that the macro
alphabetizes the sheet names? Thank you, Susan "JLatham" wrote: Copy and paste this code into the _Activate() event for the worksheet that you want to be the Table of Contents sheet: Private Sub Worksheet_Activate() Dim anySheet As Worksheet Dim myCounter As Long Cells.Clear ' remove previous content myCounter = 0 For Each anySheet In Worksheets If anySheet.Name < ActiveSheet.Name Then Range("A1").Offset(myCounter, 0) = anySheet.Name 'this adds hyperlink remove if you don't want it ActiveSheet.Hyperlinks.Add anchor:=Range("A1").Offset(myCounter, 0), _ Address:="", SubAddress:=anySheet.Name & "!A1" 'update the offset counter, keep this myCounter = myCounter + 1 End If Next End Sub I took the liberty of setting up a hyperlink in each one to take you right to that sheet if you click on one of them. Instructions on this page will show you how to get to the place you need to be to add the code to the Worksheet events section. http://www.jlathamsite.com/Teach/WorksheetCode.htm Each time you choose this worksheet the list will be updated to reflect all current sheets in the workbook except itself. Since you didn't ask for automatic sorting, it's not there. To use normal sort on the list, it's kind of tricky with cells with hyperlinks: click in one of the cells (top or bottom) and hold the mousebutton down for a short time and then drag to include the whole list and sort. You have to click and hold to select such a cell instead of just clicking, which activates the hyperlink immediately. If you don't want the hyperlinks, just leave out that one instruction (on 2 lines) and no hyperlink will be created. "nah" wrote: I have a file with 20+ worksheets that keeps increasing in size. They are not, unfortunately, in any alphabetical order. I'd like a table of contents page that I can sort by alpha, if necessary, that will update itself as new pages are added. I am using Excel 2003. |
how can I make a table of worksheet names that updates automat
Thank you so much!!!
"Don Guillett" wrote: I wouldn't bother with hyperlinks. Copy both macros into your worksheet module Now, when you activate the list will be updated and sorted. Then just double click on the cell to go the sheet desired Private Sub Worksheet_Activate() Dim i As Long Dim ms As String For i = 1 To Sheets.Count ms = Sheets(i).Name 'MsgBox ms If ms < ActiveSheet.Name Then Cells(i, 1).Value = ms End If Next i 'sorts list Columns("a").sort key1:=Cells(2, 1) End Sub '======= Private Sub Worksheet_BeforeDoubleClick _ (ByVal Target As Range, Cancel As Boolean) If Target.Column < 1 Then Exit Sub Application.DisplayAlerts = False Dim WantedSheet As String WantedSheet = Trim(ActiveCell.Value) If WantedSheet = "" Then Exit Sub On Error Resume Next If Sheets(WantedSheet) Is Nothing Then 'GetWorkbook ' calls another macro to do that Else Application.GoTo Sheets(WantedSheet).Range("a4") End If Application.DisplayAlerts = True End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Susan" wrote in message ... I am using this macro, but how can i add a step so that the macro alphabetizes the sheet names? Thank you, Susan "JLatham" wrote: Copy and paste this code into the _Activate() event for the worksheet that you want to be the Table of Contents sheet: Private Sub Worksheet_Activate() Dim anySheet As Worksheet Dim myCounter As Long Cells.Clear ' remove previous content myCounter = 0 For Each anySheet In Worksheets If anySheet.Name < ActiveSheet.Name Then Range("A1").Offset(myCounter, 0) = anySheet.Name 'this adds hyperlink remove if you don't want it ActiveSheet.Hyperlinks.Add anchor:=Range("A1").Offset(myCounter, 0), _ Address:="", SubAddress:=anySheet.Name & "!A1" 'update the offset counter, keep this myCounter = myCounter + 1 End If Next End Sub I took the liberty of setting up a hyperlink in each one to take you right to that sheet if you click on one of them. Instructions on this page will show you how to get to the place you need to be to add the code to the Worksheet events section. http://www.jlathamsite.com/Teach/WorksheetCode.htm Each time you choose this worksheet the list will be updated to reflect all current sheets in the workbook except itself. Since you didn't ask for automatic sorting, it's not there. To use normal sort on the list, it's kind of tricky with cells with hyperlinks: click in one of the cells (top or bottom) and hold the mousebutton down for a short time and then drag to include the whole list and sort. You have to click and hold to select such a cell instead of just clicking, which activates the hyperlink immediately. If you don't want the hyperlinks, just leave out that one instruction (on 2 lines) and no hyperlink will be created. "nah" wrote: I have a file with 20+ worksheets that keeps increasing in size. They are not, unfortunately, in any alphabetical order. I'd like a table of contents page that I can sort by alpha, if necessary, that will update itself as new pages are added. I am using Excel 2003. |
how can I make a table of worksheet names that updates automat
Glad to help
-- Don Guillett Microsoft MVP Excel SalesAid Software "Susan" wrote in message ... Thank you so much!!! "Don Guillett" wrote: I wouldn't bother with hyperlinks. Copy both macros into your worksheet module Now, when you activate the list will be updated and sorted. Then just double click on the cell to go the sheet desired Private Sub Worksheet_Activate() Dim i As Long Dim ms As String For i = 1 To Sheets.Count ms = Sheets(i).Name 'MsgBox ms If ms < ActiveSheet.Name Then Cells(i, 1).Value = ms End If Next i 'sorts list Columns("a").sort key1:=Cells(2, 1) End Sub '======= Private Sub Worksheet_BeforeDoubleClick _ (ByVal Target As Range, Cancel As Boolean) If Target.Column < 1 Then Exit Sub Application.DisplayAlerts = False Dim WantedSheet As String WantedSheet = Trim(ActiveCell.Value) If WantedSheet = "" Then Exit Sub On Error Resume Next If Sheets(WantedSheet) Is Nothing Then 'GetWorkbook ' calls another macro to do that Else Application.GoTo Sheets(WantedSheet).Range("a4") End If Application.DisplayAlerts = True End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Susan" wrote in message ... I am using this macro, but how can i add a step so that the macro alphabetizes the sheet names? Thank you, Susan "JLatham" wrote: Copy and paste this code into the _Activate() event for the worksheet that you want to be the Table of Contents sheet: Private Sub Worksheet_Activate() Dim anySheet As Worksheet Dim myCounter As Long Cells.Clear ' remove previous content myCounter = 0 For Each anySheet In Worksheets If anySheet.Name < ActiveSheet.Name Then Range("A1").Offset(myCounter, 0) = anySheet.Name 'this adds hyperlink remove if you don't want it ActiveSheet.Hyperlinks.Add anchor:=Range("A1").Offset(myCounter, 0), _ Address:="", SubAddress:=anySheet.Name & "!A1" 'update the offset counter, keep this myCounter = myCounter + 1 End If Next End Sub I took the liberty of setting up a hyperlink in each one to take you right to that sheet if you click on one of them. Instructions on this page will show you how to get to the place you need to be to add the code to the Worksheet events section. http://www.jlathamsite.com/Teach/WorksheetCode.htm Each time you choose this worksheet the list will be updated to reflect all current sheets in the workbook except itself. Since you didn't ask for automatic sorting, it's not there. To use normal sort on the list, it's kind of tricky with cells with hyperlinks: click in one of the cells (top or bottom) and hold the mousebutton down for a short time and then drag to include the whole list and sort. You have to click and hold to select such a cell instead of just clicking, which activates the hyperlink immediately. If you don't want the hyperlinks, just leave out that one instruction (on 2 lines) and no hyperlink will be created. "nah" wrote: I have a file with 20+ worksheets that keeps increasing in size. They are not, unfortunately, in any alphabetical order. I'd like a table of contents page that I can sort by alpha, if necessary, that will update itself as new pages are added. I am using Excel 2003. |
| All times are GMT +1. The time now is 08:38 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com