Copying a range containing cells that meet a criteria & pasting into a different sheet

[alice128]

Hi all! Basically like the title states, I need some help with writing a code that can 1. copy a range of rows that meets the criteria, and then 2. paste it into a range into another sheet.

1. In Sheet "Food Restrictions", there is table that ranges from B4:H1000 (1000 is a general placeholder, I would like to select till the last row with data). In column H of this sheet, there are cells that contain "1" and other values. I only want to select the rows from columns B:H (not the entire row) that contain "1" in column "H" and copy those rows' values to Sheet "Daily Meal" (B4:H).
2. I want the copied rows to be pasted into the table (with no blanks in between rows) starting at cell "B4" in Sheet "Daily Meal".

*I also would like for the tables' contents in Sheet "Daily Meal" to be cleared from the 4th row down before any copy & pasting happens .

Please let me know if you have any questions or need me to clear up some things. Thank you so much!

 

[alice128]

I tried recording the macro & it works, but it's extremely slow to run (takes about a minute) & freezes up. I feel like this should run faster given that there's not a lot of steps involved, but I don't know how to write the code . Any help is appreciated!

 

[Joe4]

If you post your recorded code here, we can probably help you clean it up and make it more efficient.
Recorded code is quite literal, and a lot of things, like "Selects" and "Activates" can often be deleted after being combined with other lines of code.

 

[alice128]

Thanks for the feedback, here's my recorded code:

Sub TodayMealRestrict()
'
' TodayMealRestrict Macro
'

'
Range("B4:H4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Sheets("Meal Restrictions").Select
ActiveSheet.Range("$H$3:$H$1048576").AutoFilter Field:=1, Criteria1:="1"
Range("B6:H6").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Daily Meal").Select
Range("B4").Select
ActiveSheet.Paste
Sheets("Meal Restrictions").Select
ActiveSheet.Range("$H$3:$H$1048576").AutoFilter Field:=1
Sheets("Daily Meal").Select
Range("B4").Select
End Sub

From watching the code run, I think the delay happens when I set the filter for the criteria and then when I try to reset it.

 

[Joe4]

I just noticed that you are using Excel 2021. VBA may not be necessary at all.
You may be able to use the new FILTER function to copy all the data you need to your other sheet with just one formula.
See here: FILTER function - Microsoft Support

 

[alice128]

I just noticed that you are using Excel 2021. VBA may not be necessary at all.
You may be able to use the new FILTER function to copy all the data you need to your other sheet with just one formula.
See here: FILTER function - Microsoft Support

That's true, but the reason I want to create this macro is because the people who will be using this need the list of meal restrictions on a daily basis and more importantly, I want to eliminate all potentials for errors that may occur when this is done manually (the people who are using this file aren't familiar with excel).

 

[Joe4]

I am not sure I understand why you can't use the the FILTER function. The nice thing about it is dynamic, and there is no need to clear ranges first.

So, if you go to your "Daily Meal" sheet, and enter this formula in cell B4:

=FILTER('Meal Restrictions'!B4:H1000,'Meal Restrictions'!H4:H1000=1)

It will automatically fill that sheet with all values from the "Meal Restrictions" tab that have a 1 in column H, like you wanted.
And it will automatically adjust as changes are made to column H of the Meal Restrictions sheet.

In what way does that not do what you want?

 

[alice128]

I am not sure I understand why you can't use the the FILTER function. The nice thing about it is dynamic, and there is no need to clear ranges first.

So, if you go to your "Daily Meal" sheet, and enter this formula in cell B4:

=FILTER('Meal Restrictions'!B4:H1000,'Meal Restrictions'!H4:H1000=1)

It will automatically fill that sheet with all values from the "Meal Restrictions" tab that have a 1 in column H, like you wanted.
And it will automatically adjust as changes are made to column H of the Meal Restrictions sheet.

In what way does that not do what you want?

Oh man I totally misinterpreted what you meant by the FILTER function. I thought you meant the drop down filter, didn't realize there was a FILTER function! Thanks so much mate for introducing me to this function it's pretty awesome and it did what I needed .

 

[Joe4]

You are welcome!

Yes, it is very different from the Filter menu items. Quite frankly, this new function is a real game-changer! One of the best new functions I have seen in a while!