Transferring data autonomously to another sheet with certain textSolved/Closed

Question

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

Bryx · Answer

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

TrowaD · Answer

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

Transferring data autonomously to another sheet with certain text

2 responses

Similar discussions

Subscribe To Our Newsletter!