This is untested so you might have to do some clean up.

Assuming the Workbook with the code is in the same folder.

Sub countRows()

Set NewBk = Workbooks.Add
myFolder = ThisWorkbook.Path
For Each wb In myFolder.Workbooks
For Each sh in wb.Worksheets
rc = 0
lr = sh.Cells(Rows.Count, 3).End(xlUp).Row
Set c = sh.Find("Surname", LookIn:=xlValues
If Not c Is Nothing Then
Set rng = Range("C" & c.Row +1& ":C" & lr)
rc = sh.rng.Rows.Count
End If
With NewBk.Sheets(1)
lr2 = NewBk.Sheets(1) _
.Cells(Rows.Count, 1).End(xlUP).Row
If .Range "A2" = "" Then
.Range("A2") = wb.Name
.Range("B2") = sh.Name
.Range("C3") = rc
Else
.Range("A" & lr2) = wb.Name
.Range("B" & lr2) = sh.Name
.Range("C" & lr2) = rc
End If
End With
Next
Next
End Sub



"Diddy" wrote in message
...
Hi everyone,

This is a big ask!

I need a way to count the number of rows of data in each of the workbooks
in
a folder. Some workbooks have 1 tabs, some have up to 6. The number of
rows
before the header row varies as different people have slightly different
ways
of setting up their worksheets.

What I would like to do is find "Surname" in Column C, then count number
of
rows with data after that.
Then if possible return the number of rows to a new workbook identified by
filename and sheet name if possible.

If anyone can help that would be more than fantastic!