Create automatic index

Hi Guys
Not sure if this is in the correct area or even if this can be accomplished.
I have a master spreadsheet which contains buttons that when used inserts another sheet behind the master. I want to know if, when a new sheet is inserted by using the buttons it will automatically list the new sheet on the master spreadsheet, thereby creating an index of sheets.

Where do you want the indexed sheets to be written? Cell wise of course.

Danny

Hi Danny
Thanks that's great it works, but if I wanted it in row 20?
Many thanks

Hi Mearsy,

Personally, the way I would do this is to set the code below up as a module and then tell it to call the module from your subs so that on a click of either of the buttons. Once the sheet has been created, it will be listed.

Sub ListWorksheets()
     
     'Lists all the sheet names in the workbook onto the _
    first sheet (starting at cell H1(Change the number 8 if you want this else where.))
     
    Dim i As Integer
    For i = 1 To Worksheets.Count
        Worksheets(1).Cells(i, 8) = Worksheets(i).Name
    Next i
     
End Sub

Once that is set up as a module. Write in the existing codes.

Call Module1.ListWorksheets

Regards

Danny

You mean row 20 and continue down from there?

Try

Sub GetSheetNames()

Dim wSheet As Worksheet
Dim i As Integer
i = 20
For Each wSheet In Worksheets

ActiveSheet.Cells(i, 8).Select

ActiveCell.Value = wSheet.Name

i = i + 1

Next wSheet

End Sub

This will put it in H20 down. Change the 8 if you want to change the column and if you want it to START at a different row then change where it says i = 20.

Regards

Danny

Hi Danny

This works but it puts the index in the sheet I'm inputting, I want the index on the Master Sheet. The original code worked but I just wanted to move it down the sheet, i.e. row 20.
Many thanks for your help.