Wsrtjtyk

Have been looking to solve this issue for a work spreadsheet but yet to find a solution. My workbook currently includes daily stock activity per client on individual worksheets (worksheets titled '5.9.2018, 6.9.2018, 7.9.2018 and so forth). The workbook also contains a master worksheet which is designed to provide weekly and monthly summaries of these daily worksheets (screenshot of master sheet below):

What I would like to do is incorporate a sumifs or similar function into my vba which will use the client name from the master sheet as a reference and check pre-determined columns across each daily worksheet to produce those monthly and weekly figures. Any guidance on how to incorporate this into a VBA would be greatly appreciated as I am a relative newcomer to VBA and have not been able to find the solution on my own.

Thank you

Edit: Current VBA included below

Sub AutoSum()

    Dim wscount As Long

    Dim i As Long

    wscount = ActiveWorkbook.Worksheets.Count



    For i = 1 To wscount

        Sheets(i).Select

        Range("K4").Select

        Selection.End(xlDown).Select

        ActiveCell.Offset(2, 0).Select



        Dim cel1 As String, cel2 As String

        cel1 = ActiveCell.Offset(-2, 0).End(xlUp).Address

        cel2 = ActiveCell.Offset(-1).Address

        ActiveCell.Value = "=sum(" & (cel1) & ":" & (cel2) & ")"

    Next i



    Dim rng As Range

    Dim OutApp As Object

    Dim OutMail As Object



    Set rng = Nothing

    On Error Resume Next



    Set rng = Selection.SpecialCells(xlCellTypeVisible)



    On Error GoTo 0



    If rng Is Nothing Then

        MsgBox "The selection is not a range or the sheet is protected" & _

           vbNewLine & "please correct and try again.", vbOKOnly

        Exit Sub

    End If



    With Application

        .EnableEvents = False

        .ScreenUpdating = False

    End With



    Set OutApp = CreateObject("Outlook.Application")

    Set OutMail = OutApp.CreateItem(0)



    On Error Resume Next

    With OutMail

        .To =

        .CC = ""

        .BCC = ""

        .Subject = "Today's Trades" & Date

        .HTMLBody = RangetoHTML(rng)

        .Send   'or use .Display

    End With

    On Error GoTo 0



    With Application

        .EnableEvents = True

        .ScreenUpdating = True

    End With



    Set OutMail = Nothing

    Set OutApp = Nothing

End Sub





Function RangetoHTML(rng As Range)

    Dim fso As Object

    Dim ts As Object

    Dim TempFile As String

    Dim TempWB As Workbook



    TempFile = Environ$("temp") & "" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"



    rng.Copy

    Set TempWB = Workbooks.Add(1)

    With TempWB.Sheets(1)

        .Cells(1).PasteSpecial Paste:=8

        .Cells(1).PasteSpecial xlPasteValues, , False, False

        .Cells(1).PasteSpecial xlPasteFormats, , False, False

        .Cells(1).Select

        Application.CutCopyMode = False

        On Error Resume Next

        .DrawingObjects.Visible = True

        .DrawingObjects.Delete

        On Error GoTo 0

    End With



    With TempWB.PublishObjects.Add( _

         SourceType:=xlSourceRange, _

         Filename:=TempFile, _

         Sheet:=TempWB.Sheets(1).Name, _

         source:=TempWB.Sheets(1).UsedRange.Address, _

         HtmlType:=xlHtmlStatic)

        .Publish (True)

    End With



    Set fso = CreateObject("Scripting.FileSystemObject")

    Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)

    RangetoHTML = ts.readall

    ts.Close

    RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _

                          "align=left x:publishsource=")



    TempWB.Close savechanges:=False



    Kill TempFile



    Set ts = Nothing

    Set fso = Nothing

    Set TempWB = Nothing

End Function

I don't think you even need VBA for this task. Something like this should do it for you.

=SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"rng"),criteria,INDIRECT("'"&sheets&"'!"&"sumrng")))