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 p...
excel
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)

Resources