Can anyone help me how to produce a dymamic list of worksheet names in my workbook summary page please?
Many thanks
Can anyone help me how to produce a dymamic list of worksheet names in my workbook summary page please?
Many thanks
The attached workbook creates a list of tsab names in column B of Sheet1. This is the code that does all the work.
Private Sub Worksheet_Activate()
' 28 Jul 2018
Const TargetCell As String = "B2" ' change as desired
' all rows below TargetCell will be deleted
' and the new list written starting form this cell.
Dim Arr() As Variant
Dim Target As Range
Dim i As Integer
With ThisWorkbook.Worksheets
ReDim Arr(1 To .Count)
For i = 1 To .Count
Arr(i) = .Item(i).Name
Next i
End With
Set Target = Range(TargetCell)
With Target
Range(.Cells(1), Cells(.Row + .Worksheet.UsedRange.Rows.Count, .Column)).ClearContents
Set Target = .Resize(UBound(Arr), 1)
End With
Target.Value = Application.Transpose(Arr)
End Sub
As you see (from its name) this is an event procedure. It runs when the sheet in whose class it (the code) is located is activated. This is important to know for several reasons. For one, you can change the location of the list by changing the location of the code (not the code itself, its location!). For another, the list doesn't exist or isn't updated until you ativate that sheet. Therefore, if you want to refer to that list from other sheets another method of updating it will have to be chosen.
The attached workbook has the code in action. If you change the name of any tab the list will reflect the change, except if it is Sheet1 itself. That is because the update occurs only on activation. When you change another sheet's name you must activate it first and when you go back to the list it is updated at that instance. But if you change the name of Sheet1 (the sheet with the list) is already active. The list will not be updated until you change to another sheet and change back.
When you copy the code to your own workbook make sure that you place it in the code sheet of the tab on which you wish to have the list. Of course, you must save the workbook as macro enabled therafter.
Edit:-
The code below adds a caption row to the list giving the number of listed sheets. This cell might be formatted differently from the others.
Private Sub Worksheet_Activate()
' 30 Jul 2018
Const TargetCell As String = "B2" ' change as desired
' all rows below TargetCell will be deleted
' and the new list written starting form this cell.
Dim Arr() As Variant
Dim Target As Range
Dim i As Integer
With ThisWorkbook.Worksheets
ReDim Arr(0 To .Count)
Arr(0) = Format(.Count, "0 worksheets:")
For i = 1 To .Count
Arr(i) = .Item(i).Name
Next i
End With
Set Target = Range(TargetCell)
With Target
Range(.Cells(1), Cells(.Row + .Worksheet.UsedRange.Rows.Count, .Column)).ClearContents
Set Target = .Resize(UBound(Arr) + 1, 1)
End With
Target.Value = Application.Transpose(Arr)
End Sub