VBA to display column count values of multiple worksheets

[AGrace]

Hi all,

I’m looking to create a simple summary worksheet that displays a count from the first column (a2:a5) for each worksheet in the workbook (104 – barring the first summary sheet). I was going to do this via a formula but would rather not do this 104 times for each worksheet, and wondered if there’s an easier way using VBA?

Thanks,

Adam

 

[jimrward]

How are your sheets named

 

[AGrace]

the worksheets each have unique names (peoples names).

 

[GTO]

I'm not sure what you mean by "displays a count"? Are you wanting to SUM cell A2 from each sheet, A3 from each sheet, and so on?

 

[AGrace]

On each worksheet there are tasks (1-4) displayed. Cells A2-A5 will have Task 1, 2, 3, 4 etc in them. I want the summary page to just look in these cells for each work book at count if there's a value (not the specific value of each cell though).

so sheet2 (the first sheet to look at) has 3 tasks, so next to that persons name on the summary there's a 3 count. Sheet3 has 0 tasks, so 0 next that name.

I hope that makes sense. I'm sure this is really simple to you guys, it's had me stumped.

Thanks,

 

[cb12]

If I understand you right perhaps something like the following might be of help. This assumes the first sheet is labelled summary and you want a copy of each sheets total. Obviously you can change the ranges as appropriate. If you don't want the individual totals you can always delete them manually or tack on a bit of script to delete them after copy/pastespecialing the value of the total.

Sub sheetcounter()
Dim i, j As Integer
Dim k
    i = Sheets.Count
        For j = 2 To i
            k = Application.WorksheetFunction.Sum(Sheets(j).Range("A1:A5"))
            Sheets("summary").Range("A" & j).Value = k
        Next j
    
    Range("A1").Formula = "=sum(A2:A" & i & ")"

End Sub

 

[AGrace]

Thanks cb12, it works when I change the cell values from Task 1,2,3,4 to just 1,1,1,1. However, I'm not trying to count the cell values, but rather the cells that have a value.

I tried changing the WorksheetFunction.sum to .count, but it didn't work. Is there a function that I can replace this with?

Thanks,

Adam

 

[GTO]

If the sheets are together, have you tried 'bookending' it?

=COUNTA(Sheet1:Sheet6!A1)

 

[AGrace]

I don't know what you mean by "if the sheets are together"?

This is what the workbook looks like:

Thanks,

Adam

 

[GTO]

...I’m looking to create a simple summary worksheet that displays a count from the first column (a2:a5) for each worksheet in the workbook (104 – barring the first summary sheet)....

My bad, I had forgotten what you said in the first post. Presuming the first summery sheet is before the first sheet of interest...

Insert a sheet between the first summary sheet and the first sheet of interest. Place the last summary sheet after the last sheet of interest.

Try the formula, substituting the name of inserted sheet that acts as the front 'bookend' where I have Sheet1, and Sheet6 would be replaced with teh name of your final summary sheet.

In short, the COUNTA will add the non-balnk cells for all the sheets between. You can then hide the front bookend to allow for insterting sheets later.