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

 

[AGrace]

Thanks GTO, I haven't tried your suggestion yet (as i don't quite understand it :D) but it sounds like I'd get the total number for all the sheets rather than the total per sheet, or am I totally wrong here?

 

[GTO]

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,

Okay, I think I misunderstood. I think that you could use COUNTIF, but would need to include the different sheets' names.

 

[sous2817]

Not sure if this is what you're looking for, but something like this would work:

Excel Workbook
	B	C
4	Sheet2	6
5	Sheet3	19
6	Sheet4	19
Sheet1

Excel 2007

Cell Formulas
Range	Formula
C4	=SUM(INDIRECT(B4&"!A2:A5"))
C5	=SUM(INDIRECT(B5&"!A2:A5"))
C6	=SUM(INDIRECT(B6&"!A2:A5"))

I just used the default sheet names, but if your table and your tab names match, the above formula should work.

 

[AGrace]

the table and tabs have the same names but the formula seems to fail. It just displays a #REF!

Any thoughts?

 

[sous2817]

Can you post the formula as you have it typed?

in C2 I have:

=SUM(INDIRECT(B2&"!A2:A5"))

and it works on my end.

 

[sous2817]

Wait, I was wrong. If your tabs have a space in them, it needs to look like this:

=SUM(INDIRECT("'" & B2&"'!A2:A5"))

and copied down

 

[cb12]

If you wanted to use the vba code you could simply replace the sum with CountA

 

[cb12]

Just seen there is a second page of replies and this won't calculate what you what you're looking for.

 

[AGrace]

If you wanted to use the vba code you could simply replace the sum with CountA

perfect, worked great. Thanks!


Thanks to everyone else as well!