Loop Through Worksheets and Counts Populated Rows

Hi ClaireMcF ,

I'm sending you a file to you test if the Macro works for you. The code is the following:

Sub count_indexes()

    Dim ws As Worksheet 'All Worksheets variable
    Dim CurrentSheetName As String 'Variable to store the name of the Sheet that the program is reading
    Dim CurrentSheetCount As Integer 'Variable to store the qty of rows of the Sheet that the program is reading
    Dim RowToInsert As Integer 'Variable to store the row to be filled in INDEX Sheet
        
    RowToInsert = 3 'Initially we set the row to be filled in INDEX Sheet as 3. Feel free to change it according to your use case
    
    For Each ws In Worksheets 'Loop to run all of the Sheets from the file
    
        If ws.Name <> "INDEX" Then 'Apply this block of code only if the current Sheet is not INDEX
            CurrentSheetName = ws.Name 'Store the Sheet name (note that, in your print, the stored name would be 'Andrew_Mohan', and not 'Mohan'
            Sheets(CurrentSheetName).Activate 'Activate the Sheet
            Range("A2").End(xlDown).Activate 'Check the range with populated rows
            CurrentSheetCount = ActiveCell.Row - 1 'Store the row number and subtract 1, as we have to discount the "A1" cell
            
            Sheets("INDEX").Activate 'Activate Index Sheet
        
            Cells(RowToInsert, 2).Value = CurrentSheetName 'Store Sheet name into column B (you can change it according to your use case)
            Cells(RowToInsert, 3).Value = CurrentSheetCount 'Store the qty of populated rows into column C (you can change it according to your use case)
            
            RowToInsert = RowToInsert + 1 'Increase the row counter for the next run of the loop
        End If
     
    Next ws
    
End Sub

You probably will have to make some changes to adapt it to your use case, so I added some comments to make this task easy. Please, let me know if it works for you and, if you have any question or need any help for change it, let me know.