Automatically copy rows with certain keywords in a particular column to another worksheet

Occasional Contributor

Hi All,


Scenario: From attached sheet, I want all rows that contain 'No' in the 'Available' column (E) to be automatically copied to Sheet 2. This action should be automatically executed as the parameters in Column E change without having to push an assigned macro button.


I'll really appreciate some assistance on the codes to achieve this.


Thanks guys.

4 Replies

Hi @Dazumi30 


There is no solution without a macro or script best to my knowledge, but you can use Power Automate to get this action done on schedule time/automated flow.


The best solution what I would suggest you is to make master data and generate Pivot Table & refresh your data keeping the filter as No. Just to look but dynamic add slicers to your report




Attached is the sample file for your reference.


Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official/Best Answer to help the other members find it more



With your permission, here is a small suggested solution without VBA ... is actually much faster if you have a lot of lines. Just need to filter in Sheet 2.


I would be happy to know if I could help.



I know I don't know anything (Socrates)


* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.


Thanks for your feedback. The filter does work. But the filtered table would have gaps say if items 1 and 3 meets the filter criteria and appears in sheet 2. And so on....

Is there a way I can make it like a list in table 2 irrespective of the position of the row in sheet 1 (without having empty rows)?

Thanks once again.



You can hide the existing empty lines by selecting / clicking the filter function for column E (Available) in "Table2" only "Yes".


You could also use VBA here, but with many lines that would be more time-consuming than with the filter.


Here also a VBA suggestion, in case you change your mind ... is intended for the empty lines in Table2.


Dim x as integer

x = 3
If Cells(x, 5).Value = "" Then
Rows(x).EntireRow.Hidden = True
Rows(x).EntireRow.Hidden = False
End If
x = x + 1
If Cells(x, 1).Value = "Yes" Then Exit Do

End Sub 




I would be happy to know if I could help.



I know I don't know anything (Socrates)