Conditional totals

M

MAx

HELP! I am trying to loop thorugh all the worksheets in a book and count the value of a cell if a coreesponding cell matches the desired value. I then want to report the sheet totals on a summary sheet
PROBLEM: I can separate & differentiate which total comes from which sheet
Here what I tried

Sub mv(
v1 = "sale
For z = 1 To (Sheets("NT").UsedRange.SpecialCells(xlLastCell).Row
For y = 1 To Worksheets.Coun
For Each s In Sheet
x =
If s.Cells(z, 6) = v1 Then x = x + s.Cells(z, 4
Cells(y, 1) =
Nex
Next
Next
End Su

Grateful for any help.
 
F

Frank Kabel

Hi
and where do you want to put these worksheet names?. You may use
s.name
to get the name of the processed worksheet
 
M

Max

Frank,

The dersired summary result will look like this (e.g):
Sheet Total
N 100
NE 400
E 20
SE 20
S 25
SW 60
W 420
NW 800
where the sheet is the sheet name and the total is as specified in the earlier post.

TIA
 
F

Frank Kabel

Hi
not quite sure if this will work for you but try:
Sub mv()
v1 = "sale"
For z = 1 To (Sheets("NT").UsedRange.SpecialCells(xlLastCell).Row)
For y = 1 To Worksheets.Count
For Each s In Sheets
x = 0
If s.Cells(z, 6) = v1 Then x = x + s.Cells(z, 4)
Cells(y, 1).Value = s.Name
Cells(y, 2).Value = x
Next
Next y
Next z
End Sub
 
M

Max

Got there in the end
Sub mv(
v1 = "Sale
y =
ss = Worksheets.Coun
For s = 1 To s
y = y +
x =
For z = 1 To (Sheets(s).UsedRange.SpecialCells(xlLastCell).Row
If Sheets(s).Cells(z, 6) = v1 Then x = x + Sheets(s).Cells(z, 4
Sheets("test").Cells(y, 1).Value = Sheets(s).Nam
Sheets("test").Cells(y, 2).Value =
Next
Next
End Su

Thanks
 
Top