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:
You don't need a macro to accomplish this, not if you have a current version of Excel (>=2021) or a subscription to Microsoft 365.
This formula, using the FILTER function, does the task. See the attached spreadsheet for the working example. And you can add rows of data.
=FILTER(Table1,Table1[Actual]<Table1[Expected],"none")
Here's a link that explains the FILTER function.
Hi mathetes ,
Unfortunately, I have an older version of excel and plan on sending the file to someone who has an older version as well. I tried a good old INDEX/MATCH/ROWS function to accommodate for not having the FILTER function and it didn’t work either it came up saying “#REF!” or “NA” or “VALUE” or only pastes a value from one cell.
=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:
- I am trying to modify the function to apply it to something else. In this case, I am trying to copy and paste the range of cells were the value is greater than “>0”. Unfortunately, in each modification of the function, it isn’t working like the recommendation.
One column contains a list and in another column is a SUMIF function that corresponds to the list column. I’m trying to use the function to only copy the values in that range where the SUMIF column has values “>0” and paste them on another sheet.
I’ve tried looking to see if other people have posted a similar question and gotten an answer but haven’t found anything replicable.
Again, I am open to a code that could automatically run as new data is added.
