Transferring data autonomously to another sheet with certain text

Report
Posts
1
Registration date
Wednesday December 11, 2019
Status
Member
Last seen
December 11, 2019
-
TrowaD
Posts
2599
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
March 24, 2020
-
Hello,

I want to copy the words "Pending" and "Candidates" from my main sheet called 'Test Project' to my new sheet called 'Pending Test'.

Both pending and candidates are located on column F and are within a mix of other text. Is there a way to automatically cross it over everytime I type the two words on the main sheet to the new sheet?

Thank you

2 replies

Posts
2599
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
March 24, 2020
401
Hi Achettri,

Give the following code a try:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("F")) Is Nothing Then Exit Sub
If InStr(Target.Value, "Pending") > 0 Or InStr(Target.Value, "Candidates") > 0 Then
    Target.EntireRow.Copy Sheets("Pending Test").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End If
End Sub


To implement the code, right-click the "Test Project" sheets tab and select "View code". Paste the code in the big white field.

The code will run automatically when entering data in column F.

Note: By default the code is case sensitive. When you want to also copy rows containing "pending" and "candidates" in column F, then start the code with this:
Option Compare Text


Best regards,
Trowa

1
Thank you

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 3868 users have said thank you to us this month

Posts
194
Registration date
Friday December 6, 2019
Status
Member
Last seen
February 2, 2020
4
Hi!

Do you have a very good knowledge of VBA? The programming language on Excel or content validation?

Cheers