List worksheets

[Blue]

I would like to list all the worksheet tab names starting in f5 going down.

I would also like it to update if a tab name is changed or the order of the
worksheets are rearranged is this possible.

Thanks Blue

 

[Guest]

Place this code in the VB for the worksheet you want to update and each time
you go to that sheet it will be updated... You did not mention anything about
deleting sheets so I have not taken that contingency into account. If you
need help with that let me know...

Private Sub Worksheet_Activate()
Dim rngToUpdate As Range
Dim wks As Worksheet

Set rngToUpdate = Range("F5")

Application.ScreenUpdating = False
For Each wks In Worksheets
rngToUpdate = wks.Name
Set rngToUpdate = rngToUpdate.Offset(1, 0)
Next wks
Application.ScreenUpdating = True

Set rngToUpdate = Nothing
End Sub

HTH...

 

[Harlan Grove]

Blue wrote...

I would like to list all the worksheet tab names starting in f5 going down.

I would also like it to update if a tab name is changed or the order of the
worksheets are rearranged is this possible.

Renaming worksheets triggers recalculation, so a UDF approach would be
the most efficient way to do this.


Function wsc(Optional rng As Range) As Variant
If rng Is Nothing Then Set rng = ActiveCell
wsc = rng.Parent.Parent.Worksheets.Count
End Function


Function wsl(Optional rng As Range) As Variant
Dim v As Variant, i As Long, n As Long
If rng Is Nothing Then Set rng = ActiveCell
With rng.Parent.Parent
ReDim v(1 To .Worksheets.Count)
For i = 1 To .Worksheets.Count
v(i) = .Worksheets(i).Name
Next i
End With
wsl = v
End Function


Then use it in a multiple cell array formula, e.g., select F5:F104 and
enter the array formula

=IF(ROW()-ROW($F$5)<wsc(),wsl(),"")

 

[Blue]

Thanks Jim does just what I want.

If you can remove deleted sheets as well that would an extra bonus.

Thanks Blue

 

[Blue]

Hi Halan

Thanks for the response. I can only get the first tab name to appear. I am
testing it in a workbook the four tabs, it names the first tab four times.
If I add a fifth worksheet it still only names the first tab four times
times. If I chnage the name of the first tab nothing happens. (

Blue

 

[Harlan Grove]

Thanks for the response. I can only get the first tab name to appear.
I am testing it in a workbook the four tabs, it names the first tab
four times. If I add a fifth worksheet it still only names the first
tab four times times. If I chnage the name of the first tab nothing
happens. (

Oops! The return array should be vertical. Change the udf to


Function wsl(Optional rng As Range) As Variant
Dim v As Variant, i As Long, n As Long
If rng Is Nothing Then Set rng = ActiveCell
With rng.Parent.Parent
ReDim v(1 To .Worksheets.Count, 1 To 1)
For i = 1 To .Worksheets.Count
v(i, 1) = .Worksheets(i).Name
Next i
End With
wsl = v
End Function