Transferring data autonomously to another sheet with certain text [Solved/Closed]

Report
Posts
1
Registration date
Wednesday December 11, 2019
Status
Member
Last seen
December 11, 2019
-
Posts
2702
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
March 2, 2021
-
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
2702
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
March 2, 2021
457
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

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
217
Registration date
Friday December 6, 2019
Status
Member
Last seen
July 26, 2020
4
Hi!

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

Cheers

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!