Marika,
You need to qualify the range with the active sheet:
Private Sub CommandButton1_Click()
Dim lngRow As Long
Dim Sharr(), Sh
Sharr = Array(Sheets("Sunday Night"), Sheets("Monday Morning"), Sheets("Tuesday Morning"))
For Each Sh In Sharr
lngRow = WorksheetFunction.Max(Sh.Range("a65536").End(xlUp).Row, Sh.Range("b65536").End(xlUp).Row)
Sh.Range("A" & lngRow + 2) = "Monday Total"
Sh.Range("B" & lngRow + 2).Formula = "=SumProduct(--(Weekday(L2:L" & lngRow & ") = 2))"
Sh.Range("A" & lngRow + 3) = "Tuesday Total"
Sh.Range("B" & lngRow + 3).Formula = "=SumProduct(--(Weekday(L2:L" & lngRow & ") = 3))"
Sh.Range("A" & lngRow + 4) = "Wednesday Total"
Sh.Range("B" & lngRow + 4).Formula = "=SumProduct(--(Weekday(L2:L" & lngRow & ") = 4))"
Sh.Range("A" & lngRow + 5) = "Thursday Total"
Sh.Range("B" & lngRow + 5).Formula = "=SumProduct(--(Weekday(L2:L" & lngRow & ") = 5))"
Sh.Range("A" & lngRow + 6) = "Friday Total"
Sh.Range("B" & lngRow + 6).Formula = "=SumProduct(--(Weekday(L2:L" & lngRow & ") = 6))"
Sh.Range("A" & lngRow + 7) = "Saturday Total"
Sh.Range("B" & lngRow + 7).Formula = "=SumProduct(--(Weekday(L2:L" & lngRow & ") = 7))"
Sh.Range("A" & lngRow + 8) = "Sunday Total"
Sh.Range("B" & lngRow + 8).Formula = "=SumProduct(--(Weekday(L2:L" & lngRow & ") = 1))"
Sh.Range("A" & lngRow + 9) = "Grand Total"
Sh.Range("B" & lngRow + 9).Formula = "=Sum(B" & lngRow + 2 & ":B" & lngRow + 8 & ")"
Sh.Range("A" & lngRow + 10) = "Duration Count"
Sh.Range("B" & lngRow + 10).Formula = "=Sum(J1:J" & lngRow & ")"
Sh.Range("B" & lngRow + 2, "L" & lngRow + 10).NumberFormat = "0"
Next Sh
End Sub
Regards,
Mike