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).R ow
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.

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

--
Regards
Frank Kabel
Frankfurt, Germany


MAx wrote:
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).R ow)
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) = x
Next
Next y
Next z
End Sub

Grateful for any help.

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

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).R ow)
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


--
Regards
Frank Kabel
Frankfurt, Germany


Max wrote:
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

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