[RESOLVED] Creating index sheet with copied cells

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:

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

Welcome to the forums :wave:

something like this?

vb Code:

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

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 :thumb:

Quote:

---

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

---

You are welcome :)

Quote:

---

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 :wave:

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!!)

That's Ok :)