Villalobos
New Member
- Joined
- Sep 5, 2013
- Messages
- 44
Hello, I use the below mentioned code the sum and paste the data from sheets(1) to sheets(2). My problem is that I can not exclude the 0 value from list. Could somebody help me how should I do?
Code:
Sub GetBacklog()
Dim lastRow&, filterCriteria$
filterCriteria = "<=" & Sheets("List of urgent products").Range("A1")
With Sheets(1)
lastRow = .Cells(.Rows.Count, 36).End(xlUp).Row
ThisWorkbook.Names.Add Name:="Summarize", RefersToR1C1:="='" & .Name & "'!R28C36:R" & lastRow & "C36"
End With
With Sheets(2)
Range("Summarize").AdvancedFilter xlFilterCopy, , .Range("B12"), True
.Range("C13:C500").Value = "=SUMIFS('" & Sheets(1).Name & "'!$AM$29:AM" & lastRow & ",'" & Sheets(1).Name & "'!$AK$29:AK" & lastRow & "," & Chr(34) & filterCriteria & Chr(34) & ",'" & Sheets(1).Name & "'!$AJ$29:AJ" & lastRow & "," & Sheets(2).Range("B13").Address(0, 0) & ")"
.[C12] = "Required quantity"
End With
End Sub