Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Dynamic list of worksheet names

0

Can anyone help me how to produce a dymamic list of worksheet names in my workbook summary page please?

Many thanks

Answer
Discuss

Answers

1
Selected Answer

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
Discuss

Discussion

Thanks Variatus thats fantastic and works perfect.
is there any way of making the list form a table so that i can also have the total number of sheets automatically come up? I tried doing this but when i added a sheet to the workbook it didn't automatically increase the size of the table like you might expect and overwrote the totals cell.
JonP (rep: 37) Jul 30, '18 at 2:11 am
To put the list in a table requires different coding. I have posted a modified version of the original code in my above answer. It doesn't produce a table but it adds a row with the number of sheets listed.
Variatus (rep: 4889) Jul 30, '18 at 4:29 am
Variatus
Thankyou for your help but the trouble is that I'm using the following formula that looks to the table where the list of worksheet names is:
=SUMPRODUCT(COUNTIF(INDIRECT("'"&Table23[Sheet Names]&"'!$C$3:$C$65"),C2))
and I also need a total number of sheets which is used in another formula within my summary page.
JonP (rep: 37) Jul 30, '18 at 7:01 am
This is neither fair nor productive. You asked a question and got an answer. Next time please ask a more precise question to get an answer which fits your requirements more exactly.
I shall be glad to continue helping you but not in this thread. Ask a new question. The logical way forward should be to change your formula. It presumes that the list will be in C3:C65 which is unlikely. It addresses Table23 which isn't related to C3:C65, and it uses a SUMPRODUCT function to find if a name is in the list. That looks like lighting a match in a dark room to see if the light is on. The purpose of this formula can be served in many less convoluted ways but this discussion isn't the place to talk about that.
Variatus (rep: 4889) Jul 30, '18 at 8:07 am
Variatus
Firstly let me apologise - bothe your responses worked perfectly and answered exactly what i had asked (I'll try to mark both as correct answers), its just that it didn't occur to me that the list formed wouldn't be able to follow the normal rules of a table and therefore wouldn't fit exactly with what i already had.
Maybe I'll try to produce a sample workbook to show what I'm trying to do and post this in a new thread.
I keep promising myself to learn more about creating macros for myself.
Thanks again for your help
JonP (rep: 37) Jul 30, '18 at 8:48 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login