Macro

[Marika]

I have a macro that's running on 1 worksheet, but would like to include certain other worksheets as well (Other worksheets has same data only diff. values)
TIA

 

[Ekim]

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

 

[Marika]

Hi Mike,
thanks for that.

 

[ajm]

on the compile error that i am encountering: could it be a version problem. i am using excel 97 with NT OS.

Stromma, what are you using? Hotpepper?

 

[Scott Huish]

XL2002 - Windows 2000 Pro

 

[Stromma]

I'm using xl 2000 - Win2k, xp.

I don't think that NT would be a part of the problem.

Without having any experince of xl97, i know that there are quit big difference's between are versions. I guess that it could be related xl97, but i honestly can't say if that's really the problem.

Anyone else who knows?

/Roger