Copying a cell's contents to another worksheet based on text contained in different cell

[cjames83]

Hello! If Cell 1B (sheet 1) contains the word "Pending" or "Yes", it is possible to copy the exact text from Cell 1A (sheet 1) to the next blank cell in Column A (sheet 2)? Thanks for any information!

 

[My Aswer Is This]

You can use this Vba Script:

Sub Test()
Application.ScreenUpdating = False
Dim Lastrow As Long
Lastrow = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row + 1
    If Cells(1, "B").Value = "Pending" Or Cells(1, "B").Value = "Yes" Then
        Sheets(1).Cells(1, "A").Copy Destination:=Sheets(2).Cells(Lastrow, "A")
    End If
Application.ScreenUpdating = True
End Sub

There may be some way to get content with some formula but formulas are not my game.

 

[cjames83]

Wow! Thanks so much! That's awesome! I thought something like this was possible but never knew how. Would you be able to help me a little further? Now that I know it can be done, I'd like to be a little more specific as it relates to the actual worksheet I'm working on.

Using row 5 as an example, if DT5 (sheet 1) contains the word "Pending" or "Yes", I'd like to copy the text from:
C5 (Sheet 1) to Column C in the next blank row in Sheet 2
D5 (Sheet 1) to Column B in the next blank row in Sheet 2
DV5 (Sheet 1) to Column E in the next blank row in Sheet 2
(All three should end up on the same blank row but in different cells)

Can the code be formatted so that it works every time I enter information into a new row on Sheet 1 instead of just being specific to one single row?

To give you a little background, I have a sheet that I go into everyday and add several new names to new rows. Then if column DT says pending or yes, I then have to copy the same information over to Sheet 2. I'm just trying to figure out how to automate this process to save time.

 

[My Aswer Is This]

By automatic do you mean immediately when you enter either "Yes" Or "Pending" into Column "DT"
As my previous script required you to run the Macro by clicking a button or using a shortcut key.

 

[cjames83]

ADVERTISEMENT

My apologies. Automatic wasn't what I meant. I guess I was trying to say I want the code to carry down the worksheet (apply to a range) instead of just being tied to one specific row.

 

[My Aswer Is This]

OK. I will get back with you.

 

[My Aswer Is This]

ADVERTISEMENT

Try this:

I'm the future it's always best to provide sheet names instead of saying Sheet 1 and Sheet 2

It's best to say Sheet named "Master" and Sheet named "Results"
Or something like this. As you add and move sheets around using sheet numbers can cause you problems.

Sub Pending()
Application.ScreenUpdating = False
Dim Lastrow As Long
Dim Lastrowa As Long
Lastrow = Sheets(1).Cells(Rows.Count, "DT").End(xlUp).Row
Lastrowa = Sheets(2).Cells(Rows.Count, "DT").End(xlUp).Row + 1
Dim i As Long
    
    For i = 1 To Lastrow
        If Sheets(1).Cells(i, "DT").Value = "Pending" Or Cells(i, "DT").Value = "Yes" Then
            
            Sheets(1).Cells(i, "C").Copy Destination:=Sheets(2).Cells(Lastrowa, "C")
            Sheets(1).Cells(i, "D").Copy Destination:=Sheets(2).Cells(Lastrowa, "B")
            Sheets(1).Cells(i, "DV").Copy Destination:=Sheets(2).Cells(Lastrowa, "E")
            Lastrowa = Lastrowa + 1
        End If
    Next
Application.ScreenUpdating = True
End Sub

 

[cjames83]

That worked like a charm! I also took your advice and changed it to use the worksheet names. Thanks soooo much!!!

 

[My Aswer Is This]

Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.

That worked like a charm! I also took your advice and changed it to use the worksheet names. Thanks soooo much!!!

 

[cjames83]

I'd like to see if it's possible to add one more condition which is in red. If column DT (Registration Report) says "pending" or "yes" AND column B (Registration Report) says "Both" or "Non-Scripted" then...

I tried adding the red portion of the following code but it still returned "no" values from the DT column so I obviously didn't write it correctly:

IfSheets("Registration Report").Cells(i, "DT").Value ="Pending" Or Cells(i, "DT").Value = "Yes" andSheets("Registration Report").Cells(i, "B").Value ="Both" Or Cells(i, "B").Value = "Non-Scripted"Then...

Is what I'm looking to do possible?