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

%3CLINGO-SUB%20id%3D%22lingo-sub-1728333%22%20slang%3D%22en-US%22%3EAutomatically%20copy%20rows%20with%20certain%20keywords%20in%20a%20particular%20column%20to%20another%20worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1728333%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EScenario%3A%20From%20attached%20sheet%2C%20I%20want%20all%20rows%20that%20contain%20'No'%20in%20the%20'Available'%20column%20(E)%20to%20be%20automatically%20copied%20to%20Sheet%202.%20This%20action%20should%20be%20automatically%20executed%20as%20the%20parameters%20in%20Column%20E%20change%20without%20having%20to%20push%20an%20assigned%20macro%20button.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'll%20really%20appreciate%20some%20assistance%20on%20the%20codes%20to%20achieve%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20guys.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1728333%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1728380%22%20slang%3D%22en-US%22%3ERe%3A%20Automatically%20copy%20rows%20with%20certain%20keywords%20in%20a%20particular%20column%20to%20another%20worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1728380%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F749891%22%20target%3D%22_blank%22%3E%40Dazumi30%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20is%20no%20solution%20without%20a%20macro%20or%20script%20best%20to%20my%20knowledge%2C%20but%20you%20can%20use%20Power%20Automate%20to%20get%20this%20action%20done%20on%20schedule%20time%2Fautomated%20flow.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20best%20solution%20what%20I%20would%20suggest%20you%20is%20to%20make%20master%20data%20and%20generate%20Pivot%20Table%20%26amp%3B%20refresh%20your%20data%20keeping%20the%20filter%20as%20No.%20Just%20to%20look%20but%20dynamic%20add%20slicers%20to%20your%20report%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Snag_2c3bc502.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F223025iA9ED9464DF963486%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Snag_2c3bc502.png%22%20alt%3D%22Snag_2c3bc502.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAttached%20is%20the%20sample%20file%20for%20your%20reference.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ERegards%2C%20Faraz%20Shaikh%20%7C%20MCT%2C%20MIE%2C%20MOS%20Master%2C%20Excel%20Expert%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CEM%3E%3CFONT%20color%3D%22%23808080%22%3EIf%20you%20find%20the%20above%20solution%20resolved%20your%20query%20don't%20forget%20mark%20as%26nbsp%3B%3CSPAN%3EOfficial%2FBest%20Answer%26nbsp%3Bto%20help%20the%20other%20members%20find%20it%20more%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FEM%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1728478%22%20slang%3D%22de-DE%22%3ESubject%3A%20Automatically%20copy%20rows%20with%20certain%20keywords%20in%20a%20particular%20column%20to%20another%20worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1728478%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F749891%22%20target%3D%22_blank%22%3E%40Dazumi30%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EWith%20your%20permission%2C%20here%20is%20a%20small%20suggested%20solution%20without%20VBA%20...%20is%20actually%20much%20faster%20if%20you%20have%20a%20lot%20of%20lines.%3C%2FSPAN%3E%20%3CSPAN%3EJust%20need%20to%20filter%20in%20Sheet%202.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20happy%20to%20know%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%222%22%3E*%20Kindly%20Mark%20and%20Vote%20this%20reply%20if%20it%20helps%20please%2C%20as%20it%20will%20be%20beneficial%20to%20more%20community%20members%20reading%20here.%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
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

 

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.

@Nikolino,

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)