Forum Discussion
VBA Code to Automatically Copy and Paste a Range of Data if the Criteria is Met
=IFERROR(INDEX(Sheet2!D$81:D$86,SMALL(IF(Sheet2!$F$81:$F$86>Sheet2!$G$81:$G$86,ROW(Sheet2!$A$81:$A$86)-80),ROW($A1))),"")Without VBA you can try this formula. The formula has to be entered as an arrayformula with ctrl+shift+enter if one doesn't work with Office 365 or Excel 2021. In the example the formula is in cell H2 of sheet8 and filled across range H2:K8.
Sheet2:
Sheet8:
Thank you for the detailed explanation. You can try this formula which must be entered as an arrayformula with ctrl+shift+enter if one doesn't work with Office 365 or Excel 2021.
=IFERROR(INDEX(Sheet6!A$3:A$1000,SMALL(IF(Sheet6!$B$3:$B$1000>0,ROW(Sheet6!$A$3:$A$1000)-2),ROW(Sheet6!$A1))),"")The formula is in cell E42 of Sheet7 and filled across range E42:F51. The formula can be filled down and the ranges in the formula can be adapted from e.g. Sheet6!A$3:A$1000 to Sheet6!A$3:A$20000.
An alternative could be these lines of code. In the attached file you can click the button in cell H38 of Sheet7 to run the macro.
Sub summary()
Dim i, j, k As Integer
Sheets("Sheet7").Range("H42:I1048576").Clear
j = Sheets("Sheet6").Range("A" & Rows.Count).End(xlUp).Row
k = 42
For i = 3 To j
If Sheets("Sheet6").Cells(i, 2).Value > 0 Then
Sheets("Sheet7").Cells(k, 8).Value = Sheets("Sheet6").Cells(i, 1).Value
Sheets("Sheet7").Cells(k, 9).Value = Sheets("Sheet6").Cells(i, 2).Value
k = k + 1
Else
End If
Next i
End Sub
In cell B3 of Sheet6 i've entered a shorter formula which must be entered as an arrayformula with ctrl+shift+enter if one doesn't work with Office 365 or Excel 2021. The ranges of the formula can be adapted as required.
=SUM(IF(Sheet7!$B$3:$J$8=Sheet6!A3,Sheet7!$C$3:$K$8))
Once again, thank you!