Transferring data autonomously to another sheet with certain text

Solved/Closed
achettri Posts 1 Registration date Wednesday December 11, 2019 Status Member Last seen December 11, 2019 - Dec 11, 2019 at 11:56 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Dec 17, 2019 at 12:00 PM
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
Related:

2 responses

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Updated on Dec 17, 2019 at 12:01 PM
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

Bryx Posts 218 Registration date Friday December 6, 2019 Status Member Last seen December 22, 2021 4
Dec 13, 2019 at 02:21 AM
Hi!

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

Cheers