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