Forum Discussion

Dazumi30's avatar
Dazumi30
Copper Contributor
Sep 30, 2020

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

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.

excel

4 Replies

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Sep 30, 2020

    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.

    • Dazumi30's avatar
      Dazumi30
      Copper Contributor
      Sep 30, 2020
      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.
      • NikolinoDE's avatar
        NikolinoDE
        Platinum Contributor
        Sep 30, 2020

        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)

  • ExcelExciting's avatar
    ExcelExciting
    MVP
    Sep 30, 2020

    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

Resources