Hi All Good people
I have a workbook and macro to calculate sum from multiple worksheets and put it to master sheet. Below is my current macro which works fine. But I need to add one more additional condition to SUM. I think this can be done by using sumif. I have attached my workbook below explaining about my current outcome and expected outcome. Kindly have a look
Sub GetSums()
Dim lngCol As Long, wsName As Range
With Sheets("Master")
If .[C3] <> "" Then .Range("C3", .[B3].End(2)) = ""
For Each wsName In .Range("C2", .[B2].End(2))
lngCol = Sheets(wsName.Value).Rows(1).Find("My Text").Column
wsName.Offset(1).Value = _
Evaluate("Sum('" & wsName.Value & "'!" & Cells(3, lngCol + 1).Address & ":" & _
Sheets(wsName.Value).Cells(Rows.Count, lngCol + 1).End(3).Address & ")")
Next wsName
End With
End Sub
I have a workbook and macro to calculate sum from multiple worksheets and put it to master sheet. Below is my current macro which works fine. But I need to add one more additional condition to SUM. I think this can be done by using sumif. I have attached my workbook below explaining about my current outcome and expected outcome. Kindly have a look
Msaster Workbook.xlsx
docs.google.com
Sub GetSums()
Dim lngCol As Long, wsName As Range
With Sheets("Master")
If .[C3] <> "" Then .Range("C3", .[B3].End(2)) = ""
For Each wsName In .Range("C2", .[B2].End(2))
lngCol = Sheets(wsName.Value).Rows(1).Find("My Text").Column
wsName.Offset(1).Value = _
Evaluate("Sum('" & wsName.Value & "'!" & Cells(3, lngCol + 1).Address & ":" & _
Sheets(wsName.Value).Cells(Rows.Count, lngCol + 1).End(3).Address & ")")
Next wsName
End With
End Sub