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

Copper 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

 

Snag_2c3bc502.png

 

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

@Dazumi30 

 

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.

 

Nikolino

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.

@NikolinoDE,

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.

@Dazumi30 

 

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

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

End Sub 

'Untested

 

 

I would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)