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

cjames83

New Member
Joined
Jan 6, 2017
Messages
24
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!
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,264
Office Version
  1. 2013
Platform
  1. Windows
You can use this Vba Script:
Code:
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.
 
Last edited:

cjames83

New Member
Joined
Jan 6, 2017
Messages
24
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

Well-known Member
Joined
Jul 5, 2014
Messages
17,264
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Jan 6, 2017
Messages
24

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

Well-known Member
Joined
Jul 5, 2014
Messages
17,264
Office Version
  1. 2013
Platform
  1. Windows

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.

Code:
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
 
Last edited:

cjames83

New Member
Joined
Jan 6, 2017
Messages
24
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

Well-known Member
Joined
Jul 5, 2014
Messages
17,264
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Jan 6, 2017
Messages
24
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,383
Messages
5,601,318
Members
414,441
Latest member
KellyTheKid

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top