[RESOLVED] Creating index sheet with copied cells

[Trivvie]

I have a spreadsheet with 50 worksheets all created from the same template. I want to create a front sheet that lists the names of the worksheets in column A and then in column B copy the data from G10 and then in column C copy the data from H10 from each of the worksheets.

Currently I create a new worksheet and then use the following code:

Sub Sheetlister()

Dim Sheet As Object
Range("A2").Select
For Each Sheet In ActiveWorkbook.Sheets
ActiveCell.Formula = Sheet.Name
ActiveCell.Offset(1, 0).Select
Next Sheet

End sub

Can you help with adapting the code so that I can copy my data cells from each named sheet to the this sheet.

Thank you

[Siddharth Rout]

Welcome to the forums

something like this?

Sub PopulateData()
    Dim i As Long, shtName As String, rowNo As Long
    
    '~~> Replace "Sheet1" below with the name of the sheet
    '~~> where you want to display data
    shtName = "Sheet1"
    '~~> Row number from where you want to start putting data
    rowNo = 1
    
    '~~> Loop thru the sheets in the workbook
    For i = 1 To Sheets.Count
        '~~> Checking if current sheet is not "Sheet1"
        If Sheets(i).Name <> shtName Then
            '~~> Get relevant values
            Sheets(shtName).Range("A" & rowNo).Value = Sheets(i).Name
            Sheets(shtName).Range("B" & rowNo).Value = Sheets(i).Range("G10").Value
            Sheets(shtName).Range("C" & rowNo).Value = Sheets(i).Range("H10").Value
            '~~> Increment row count
            rowNo = rowNo + 1
        End If
    Next i
End Sub

[Trivvie]

What a way to welcome me - a brilliant wave and a solution!!!!!!!

Thank you so much, I had posted this on another forum and didn't get an answer that worked and I struggled to understand their code and I can work my way through yours and can now see how to adapt it for another worksheet I needed to do.

Thanks again

[Siddharth Rout]

What a way to welcome me - a brilliant wave and a solution!!!!!!!

You are welcome

I had posted this on another forum and didn't get an answer that worked and I struggled to understand their code

That is why VB Forums is the best

Well, if your query is solved then do remember to mark this thread resolved. Check the link in my signature on how to mark a thread resolved

[Trivvie]

I had already - did you see the green tick, but I put it against my bit and not the thread sorry (Thinking I thought I had got it right, but I didn't!!)

[Siddharth Rout]

That's Ok