Sep 30 2020 02:53 AM
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.
Sep 30 2020 03:09 AM
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
Sep 30 2020 03:49 AM
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.
Sep 30 2020 06:52 AM
Sep 30 2020 09:45 AM
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)