Forum Discussion
VBA Code to Automatically Copy and Paste a Range of Data if the Criteria is Met
Hi,
I am looking for some help with a copy and paste code.
I want excel to determine if the values in one column is less than the values in another and if so, copy/paste those rows of data into another sheet. Since more information would be added to the original data set, I also want excel to copy/paste the new data into the next available row on the other sheet.
The codes I wrote to check if the values in one column < the values in another column weren’t running so I decided to create a helper column that runs the formula and returns True/False. So my current VBA code is written to see if “True” is in that range and to copy those rows of data and paste them where I want after resizing it to omit that helper column.
Unfortunately, when I run the code it is only pasting the last row that tests true (but in that PasteCell I can see it run through all the rows that are True and it completes the paste only of the last row that is True.)
I’ve added an image of the code for reference.
Could someone please help me spot why it’s not pasting the desired range or help me rewrite another code that will?
Thanks!!
=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:
9 Replies
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:
