create sumtable of worksheets names + some data

[DutchKevin]

Hi Guys, I'm sure i've seen a sollution here or there in the past, but i'm unable to find it again. I'm a newby to VBA but trying hard to catch up.
Here's my case:
I want on workbook_open to automatically list on sheet1 a table with all the other sheetnames (Sheet1,Sheet2,Sheet3..etc).
Next to that namecolumn I would like to present the data of each sheet from certain fixed cells. Always cell B5, D5 or others.
The Mastersheet must automatic, and the following sheets are all identical in layout.

Thanks for helping!
Kevin

 

[Yard]

Try this

Sub ListSheets()
Dim l As Long, wks As Worksheet

For Each wks In ThisWorkbook.Worksheets
    If wks.Name <> Sheet1.Name Then
    l = l + 1
        With Sheet1
            .Cells(l, 1).Value = wks.Name
            .Cells(l, 2).Value = wks.Range("B5").Value
        End With
    End If
Next wks

End Sub

 

[DutchKevin]

Hi Yard,
Thanks a lot for the help. It works exactly like I needed.
Looking at it, it's not so hard to follow how it works.
I'll tune it to my needs and once again: Thanks!

Kevin