Solved: 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(TO DAY()),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