Loop Through Worksheets and Counts Populated Rows

[ClaireMcF]

I want to create a macro that I can run each week.  I have a workbook and on the first worksheet I want to count how many populated rows there are on the other worksheets. So in this example I want a count of all populated rows on the Andrew/Mohan worksheet to populate on the 'Index' worksheet under 'Overdue' & Mohan, then a count of all populated rows on the 'Arun' worksheet under 'Overdue' and so on until all of the sheets are checked and the 'Index' worksheet is populated.  Then the same for 'Upcoming'.  At the moment I am having to use a formula on each line on the 'Index' worksheet and change it based on what is column B (=COUNTA(Mohan!$A$2:$A$40)The data on the other sheets has to start from cell A2 and count until a row is empty. However, there may not be a separate worksheet for each person every week, so the loop has to recognize that there is no sheet for a persons name, where that is the case each week.

[#2]

@ClaireMcF 

I can't write the macro for you. But I have a suggestion to try it with a function.

If B2 contains the name of the worksheet. For example, "Andrew_Mohan". Then:

=IFERROR(COUNTA(INDIRECT(B2&"!A2:A40")),"")

[#3]

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.

[#4]

Hi @rzaneti

Thanks for your reply - this is really helpful. I have tried to run the code on my workbook, but I am getting a debug error at the :

CurrentSheetCount = ActiveCell.Row - 1 'Store the row number and subtract 1, as we have to discount the "A1" cell

and the code does not execute.

Can you help at all please?

Thanks
Claire

[#5]

Hi, @ClaireMcF .

There are some possible reasons for an error at this line of code. Could you please send an image of the error message?

One of the possible problems is the error 6 (overflow), which will happen if we have an empty Sheet or if you have a specific Sheet with a large number of rows (more than 32,767 rows). Can you confirm if you have any of these cases in your file?

[#6]

If you're using Excel 365, the solution is simple (VSTACK and a few other functions) and does not require a line of code.

[#7]

Thanks Patrick - can you elaborate please? The simpler the better for me, as I am by no means an excel expert. Thanks in advance.

[#8]

Thanks so much - I don't have an empty sheet but when I ran the code for the first time it jumps to the 'Mohan' worksheet and settles on row 1048576 and I get the 6 (overflow) error that you mention below. So I deleted the empty rows from row 3 to row 1048576 and ran the code again. Same thing happens, and the cursor settles on Mohan, row 1048576. Thankyou

[#9]

@ClaireMcF 

The formula would look like something like this:

=LET(Stack,VSTACK('A:C'!$A$2:$C$100000),filtered,FILTER(Stack,TAKE(Stack,,1)<>""),ROWS(filtered))

In this demo, I have sheets A, B, C.  Please see attached workbook. 

[#10]

So that works for me, but for one sheet only. I had replaced 'A:C' with the name of my first worksheet (Mohan) to the name of my last worksheet (Terry), so it reads =LET(Stack,VSTACK'Mohan:Terry''!$A$2:$C$100000),filtered,FILTER(Stack,TAKE(Stack,,1)<>""),ROWS(filtered)) and I get a #NUM error. If I just use Mohan, then it works but I have to replace the name as I move down each row

[#11]

Are you able to share an anonymized copy of the workbook?

Edit: here's the formula with your sheet names:

=LET(Stack,VSTACK(Mohan:Terry!$A$2:$C$100000),filtered,FILTER(Stack,TAKE(Stack,,1)<>""),ROWS(filtered))

Here's my sheet placement (notice the summary is right of the sheets I'm totaling:

[#12]

@Patrick2788 

[#13]

That's exactly what I changed, but it doesn't seem to work for me. It'll be operator error! :)

[#14]

@ClaireMcF 

Some of the sheets contained spaces after the names in column A. I've went through the workbook and removed those.

I've also include two possible solutions.

The second option uses REDUCE and calls the Lambda 'Milestone':

=REDUCE(header,manager,Milestone)

[#15]

Thanks for this - super useful. I probably need to spend a bit of time understand the formulas and playing around with them, as I can't get them to work at my end. I must be doing something wrong. I'm not sure I understand the 'REDUCE' solution. Thanks again

[#16]

Hi, sorry for delayed response. I am still looking at this as I can't get your macro to run properly. Need to spend some time just having a run through it. Thanks