Sum Across unknown Number of Worksheets

[Nurofen]

Hi,
I have creating the sheet, coping the Master copy over to the sheets and the zoom sorted with help from forum users . Use the Create Tab to create the sheets to have a better understanding.

My problem is that I need the Week Totals to sum up the sheets before them and the Monthly total to sum up the Week Totals. Every month they will be different due to amount of days in each month. I was hoping if someone could help me out with code to make this work. I have marked the ranges in Red.

Hope I have been clear.

Thank you for your time

Nurofen

[Bob Phillips]

Very difficult without sheets for Sunday.

[Nurofen]

o well

Can the code not act as if Week1,Week2,Week3,Week4 and if the is a Week5 in place Sunday?

What about week1 and the dated sheets before it and then again for the other weeks and then the weeks to the month. A different code for each one. If anyone has any ideas I will be very happy to hear them.

Thank you for your time

Nurofen

[Bob Phillips]

Not the formula I was thinking of, as it works on a contiguous date range.

For the weeks, I would just use


=SUMPRODUCT(SUMIF(INDIRECT("'WEEK "&ROW(INDIRECT("1:4"))&"'!a1:A10"),"<>"))+IF(ISERROR(SUM('WEEK 5'!A1:A10)),0,SUM('WEEK 5'!A1:A10))

[Nurofen]

Hi,

I meant can it be done in code at a click of a button
Sorry for not being clear.


Thank you for you time


Nurofen

[Shazam]

Not the formula I was thinking of, as it works on a contiguous date range.

For the weeks, I would just use


=SUMPRODUCT(SUMIF(INDIRECT("'WEEK "&ROW(INDIRECT("1:4"))&"'!a1:A10"),"<>"))+IF(ISERROR(SUM('WEEK 5'!A1:A10)),0,SUM('WEEK 5'!A1:A10))

Hi xld


I don't understand the op question. If possible can you post a small example what your formula is doing? If not thats ok.

[Bob Phillips]

Shaz,

It is very similar to the Week formula that I posted, excep that I build an array of dates for this month.

[Shazam]

Shaz,

It is very similar to the Week formula that I posted, excep that I build an array of dates for this month.
Thanks I will experiment it to understand further. By using the op workbook.

[Bob Phillips]

That's the problem Shaz, because it doesn't have Sunday worksheets, that technique will not work because the array builder


"'"&TEXT(ROW(INDIRECT(DATE(YEAR(TODAY()),MONTH(TODAY()),1)&":"&DATE(YEAR(TODAY()),MONTH(TODAY())+1,0))),"ddd dd-mm-yy")&"'!a1:A10"

will create an entry for every date.

[Nurofen]

Shaz,
I need for the VBA code to build the sats sheets,copy the Master over, Zoom it to 75% and where every Weeks sheets lay i need the code to calculate the Dated sheets before it
The ranges are B6:E24, G6:H24, K6:P24
The ranges are B28:E49, G28:H49, K28:P49
and then the Month End Total to calculate the Weeks Totals same ranges, but they may change when new agents are added or leave.


Thank you for all the time Xld, Shaz and anyone else is putting into finding a solution to my question.

Thankyou you all for your time

Nurofen

[Bob Phillips]

What determines what days go in Week 1 etc.?

[Nurofen]

xld,

The amount of days in Week Total is determined by the Calendar Month,
(March 2007)
If the 1st of month is say Thursday then week1 will have Thursday,Friday,Saturday and Week1 Total.

Week2 Total,Week3 Total,week4 Total,Week5 Total will all have Monday,Tuesday,Wedesday,Thursday,Friday and Saturday, after the Week 5 Total will be March End Total

If you change the Calendar Month on your PC and then run the code you can get a better understanding.

I hope this answers your question

Thankyou again for you time

Nurofen

[Nurofen]

Hi to all helping me or looking in to it.

I was just wondering is this something that just can not be done?

Thanking for your time

I'm open to other ways of working this out.

Nurofen

[Nurofen]

I have recorded a macro to calculate the 1st week in October is it possible to change the code so it will not look for the Tab Names below
but any Tabs before Week1 Tab



Range("B6").Select
ActiveCell.FormulaR1C1 = _
"='Mon 01-10-07'!RC+'Tue 02-10-07'!RC+'Wed 03-10-07'!RC+'Thu 04-10-07'!RC+'Fri 05-10-07'!RC+'Sat 06-10-07'!RC"
Range("B6").Select
Selection.Copy
Range("B7:B24").Select
ActiveSheet.Paste
Range("C6").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"='Mon 01-10-07'!RC+'Tue 02-10-07'!RC+'Wed 03-10-07'!RC+'Thu 04-10-07'!RC+'Fri 05-10-07'!RC+'Sat 06-10-07'!RC"
Range("C6").Select
Selection.Copy
Range("C7:C24").Select
ActiveSheet.Paste
Range("D6:E24").Select
ActiveSheet.Paste
Range("K6:P24").Select
ActiveSheet.Paste
Range("C28:E49").Select
ActiveSheet.Paste
Range("K28:P49").Select
ActiveSheet.Paste
Range("B24").Select
Application.CutCopyMode = False
Selection.Copy
Range("B28:B49").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-24
Range("G6").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"='Mon 01-10-07'!RC+'Tue 02-10-07'!RC+'Wed 03-10-07'!RC+'Thu 04-10-07'!RC+'Fri 05-10-07'!RC+'Sat 06-10-07'!RC"
Range("G6").Select
Selection.Copy
Range("G7:G24").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=6
Range("G28:G49").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-12
Range("H6").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"='Mon 01-10-07'!RC+'Tue 02-10-07'!RC+'Wed 03-10-07'!RC+'Thu 04-10-07'!RC+'Fri 05-10-07'!RC+'Sat 06-10-07'!RC"
Range("H6").Select
Selection.Copy
Range("H7:H24").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=9
Range("H28:H49").Select
ActiveSheet.Paste
Range("H36").Select
End Sub


Thank you for your time

Nurofen

[Nurofen]

Thanks to all that have helped and for all those that have tried.
I have now found the answer, may it help others.



Sub ABC()
Dim sh As Object, sh1 As Object
Dim sh2 As Object, Sh3 As Object
Dim ar As Range, s1 As String
Dim s As String, s2 As String
Dim i As Long, rng As Range
Dim v() As Object
ReDim v(1 To 6)
s2 = "B6:E24,G6:H24,K6:P24,B28:E49,G28:H49,K28:P49"

i = 0
For Each sh In ActiveWorkbook.Sheets
Select Case Left(sh.Name, 3)
Case "Sat", "Mon", "Tue", "Wed", "Thu", "Fri"
If sh1 Is Nothing Then
Set sh1 = sh
End If
Set sh2 = sh
Case "WEE"
If Not sh1 Is Nothing Then
s = "'" & sh1.Name & ":" & sh2.Name & "'!"
Set rng = sh.Range(s2)
For Each ar In rng.Areas
ar.Formula = "=Sum(" & s & _
ar(1).Address(0, 0, xlA1) & ")"
Next
End If
i = i + 1
Set v(i) = sh
Set sh1 = Nothing
Set sh2 = Nothing
Case Else
If InStr(1, sh.Name, "MONTH", vbTextCompare) Then
Set Sh3 = sh
Set sh1 = Nothing
Set sh2 = Nothing
End If
End Select
Next
If Not Sh3 Is Nothing Then
s = ""
For i = LBound(v) To UBound(v)
On Error Resume Next
Set sh = v(i)
On Error GoTo 0
If Not sh Is Nothing Then
s = s & "'" & sh.Name & "'!ZZZ,"
End If
Next
s = Left(s, Len(s) - 1)
Set rng = Sh3.Range(s2)
For Each ar In rng.Areas
s1 = Replace(s, "ZZZ", ar(1).Address(0, 0))
ar.Formula = "=Sum(" & s1 & ")"
Next
Else
MsgBox "No Monthly Sheet found"
End If
End Sub


Nurofen

[Shazam]

Good jod Nurofen! I glad you were persistent to get this solved. I'm really curious how this work. If you can post your workbook with a Before and after results. That will be great.

[Nurofen]

Hi Shazam,

My 1st post is the before and I have attached the after.

I still need more help to to get it working the way I want, but as they say least I can now move one step closer.
Version 1 is working for the users and it a good feeling thank you all

Nurofen