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!
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
18,689
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
18,689
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
18,689
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
18,689
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?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,851
Messages
5,833,967
Members
430,249
Latest member
Muka

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