SUMIFS across multiple worksheets IF the sheet name meet the criteria with VBA

[Oozden]

Hi All,

I have a dynamic range of sheets that is updated daily (new sheet added daily with the same format but updated values). I need to modify the code below to go through all worksheets and use sheet name as a criteria.

For example, if I type date 20.02.2019 on Sheet1.Range("A1"), it needs to find the worksheet named as 20.02.2019 and run the code.

Basically I need to see the daily values on Sheet1, which I want to see.

Creating the structure in my mind was really complex for me. I hope I have explained clear enough for you guys

Dim lRow As Long
Dim i As Long


Sheets(1).Activate


'Find the last non-blank cell in Range M:M (Column 13)
lRow = Sheets(1).Cells(Rows.Count, 13).End(xlUp).row
    
    
For i = 3 To lRow


    Sheets(1).Cells(i, 24).Value = WorksheetFunction.SumIfs(Sheets(2).Range("O:O"), Sheets(2).Range("D:D"), Cells(i, 13), _
Sheets(2).Range("X:X"), Range("X1"), Sheets(2).Range("Y:Y"), Range("Y1"))
    
Next i

 

[sijpie]

It would look something like:

Dim lRow As Long
Dim i As Long
dim wsLU as worksheet
dim sDate as string


'Sheets(1).Activate 'no need to activate sheets, that just takes time

'get the date from sheet 1 A1
sDate = sheets(1).range("A1").text

'set the worksheet object to the sheet with the same name. _
 We need an error trap here in case the sheet doesn't exist
on Error resume next
set wsLU = sheets(sDate)
on Error Goto 0   'restore error behaviour
'check if sheet exists
if wsUL is nothing then
    Msgbox "Error - Sheet name " & sDate & " does not exist"
    Exit sub
end if



'Find the last non-blank cell in Range M:M (Column 13)
lRow = Sheets(1).Cells(Rows.Count, 13).End(xlUp).row
    
    
For i = 3 To lRow

With wsLU

    Sheets(1).Cells(i, 24).Value = WorksheetFunction.SumIfs(.Range("O:O"), _
    .Range("D:D"), Cells(i, 13),.Range("X:X"), Range("X1"), .Range("Y:Y"), Range("Y1"))

End With
    
Next i