Counting thru VBA

[Karen]

Hello
Through VBA I need to enter the Counta formula in each of 50
worksheets in a workbook to count the number of entries therein minus
the header.
I then want to list on a main sheet the tab name of each sheet and the
result of the counta on each of those worksheets alongside.
Does this make any sense?
Is this at all possible?

Karen

 

[Dave Peterson]

Something like this might get you started:

Option Explicit
Sub testme()

Dim mstrWks As Worksheet
Dim wks As Worksheet
Dim iCtr As Long

Set mstrWks = Worksheets("sheet1")
iCtr = 0
For Each wks In ActiveWorkbook.Worksheets
If wks.Name = mstrWks.Name Then
'do nothing
Else
iCtr = iCtr + 1
mstrWks.Cells(iCtr, 1).Value = wks.Name
mstrWks.Cells(iCtr, 2).Value _
= Application.CountA(wks.Range("a:a")) - 1
End If
Next wks

End Sub

 

[Karen]

Thanks Dave simple and works like a charm