Look at Sheets that begin with Shop* then see if data exists in Range...

[menor59]

Hello,

and thank you for taking a peak..heres the situation...

I was wondering if this was dooable in vba..

Look at Sheets that begin with Shop*

From those sheets check to see if data exists in Range...L7:L62.

If so sum the number of instances and please them on the Totals sheet n20.

In summary...

worksheet shop 1134 range L7:L62...data is there in L20, L25, and L30
worksheet shop 1154 range L7:L62...data is there in L21, L30, and L40
worksheet shop 1112 range L7:L62...data is there in L29, L34, and L41
worksheet shop 1176 range L7:L62...data is there in L26, L45, and L56, and L61
worksheet shop 1140 range L7:L62...data is there in L22, L57, and L62

Because there is data on those rows From sheets that are named "Shop*" I should be able to goto the totals sheet and see the number 16 in cell N20.

Thoughts?

[mancubus]

hi menor59.
try this.


Sub CheckSheetsCount()

Dim ws As Worksheet
Dim ttl As Long

For Each ws In Worksheets
With ws
If InStr(1, .Name, "Shop") > 0 Then
ttl = ttl + Application.CountA(.Range("L7:L62"))
End If
End With
Next

With Worksheets("Total")
.Activate
.Range("N20").Value = ttl
End With

End Sub