if Specific cell contains specific text then copy paste value from cell x to cell y

[Summer7sun]

I am very new to coding need help with the code," Sorry for my mistakes its my first post"
I am trying to copy data from Cell E8 to Cell AB8 if Cell T8 has Yes and if T8 has no the want to copy E8 to AC8 and want it to do the same thing for T9,T10,T11 till T108.

I have this Successfully working on only T8 Row want the same for other cells till T108. I really dont know how to go about this further Please Help. Thank You

If Range("T8") = "Yes" Then
Sheets("Engine").Select
Sheets("Engine").Name = "Engine"
Range("E8").Select
Selection.Copy
Range("AB8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("AB3").Select
Application.CutCopyMode = False
End If

If Range("T8") = "No" Then
Sheets("Engine").Select
Sheets("Engine").Name = "Engine"
Range("E8").Select
Selection.Copy
Range("AC8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("AB3").Select
Application.CutCopyMode = False
End If

 

[JackDanIce]

Welcome to the board. Try:

Sub Update_Col()
    
    Application.ScreenUpdating = False
    
    If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
    
    With Range("E8:AC108")
        On Error Resume Next
        .AutoFilter
        .AutoFilter Field:=16, Criteria1:="Yes"
        .Offset(1).Resize(.Rows.count - 1, 1).SpecialCells(xlCellTypeVisible).Copy
        Range("AB8").PasteSpecial xlPasteValues
        Application.CutCopyMode = False
        ActiveSheet.ShowAllData
        .AutoFilter Field:=16, Criteria1:="No"
        .Offset(1).Resize(.Rows.count - 1, 1).SpecialCells(xlCellTypeVisible).Copy
        On Error GoTo 0
    End With
    
    Range("AC8").PasteSpecial xlPasteValues
    ActiveSheet.AutoFilterMode = False
    
    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
    End With
        
End Sub

 

[Summer7sun]

Welcome to the board. Try:

Sub Update_Col()
    
    Application.ScreenUpdating = False
    
    If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
    
    With Range("E8:AC108")
        On Error Resume Next
        .AutoFilter
        .AutoFilter Field:=16, Criteria1:="Yes"
        .Offset(1).Resize(.Rows.count - 1, 1).SpecialCells(xlCellTypeVisible).Copy
        Range("AB8").PasteSpecial xlPasteValues
        Application.CutCopyMode = False
        ActiveSheet.ShowAllData
        .AutoFilter Field:=16, Criteria1:="No"
        .Offset(1).Resize(.Rows.count - 1, 1).SpecialCells(xlCellTypeVisible).Copy
        On Error GoTo 0
    End With
    
    Range("AC8").PasteSpecial xlPasteValues
    ActiveSheet.AutoFilterMode = False
    
    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
    End With
        
End Sub

first of all I woud like to thank you for your reply
However the code is not exactly working as i wanted


E8 to E108 has some data
T8 to T108 pulls up data when a criteria is met (if the critera is met it says yes or no if the condition is not met it is blank)


now coloumn AB is for Yes and AC is for no


eg if T10 is yes then i want to copy the data from E10 and paste special "only the value" to AB10 same row
now at any point if T10 value changes to No then want to copy E10 to AC10 on the same row




and same for other cells to their respected row cells

 

[JackDanIce]

I've put Yes/No values into column T and alternating 1 and 0 into column E, across range E8:T108 and it copies the correct values to columns AB and AC respectively.

Can you specifically explain how it is not exactly working as you wanted? I can't see your PC screen to understand what you mean.

Assuming headers are in row 8 and data to be posted to AB9 and AC9 respectively, this appears to work:

Sub Update_Col()
    
    Application.ScreenUpdating = False
    
    If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
    
    With Range("E8:AC108")
        On Error Resume Next
        .AutoFilter
        .AutoFilter Field:=16, Criteria1:="Yes"
        .Offset(1).Resize(.Rows.count - 1, 1).SpecialCells(xlCellTypeVisible).Copy
        Range("AB9").PasteSpecial xlPasteValues
        Application.CutCopyMode = False
        ActiveSheet.ShowAllData
        .AutoFilter Field:=16, Criteria1:="No"
        .Offset(1).Resize(.Rows.count - 1, 1).SpecialCells(xlCellTypeVisible).Copy
        On Error GoTo 0
    End With
    
    Range("AC9").PasteSpecial xlPasteValues
    ActiveSheet.AutoFilterMode = False
    
    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
    End With
        
End Sub

 

[JackDanIce]

Sorry it wasn't pasting to the same row, instead try:

Sub Update_Col2()
    
    Dim x   As Long
    Dim col As Long
    
    Application.ScreenUpdating = False
    
    For x = 8 To 108
        If Len(Cells(x, 5).Value) > 0 Then
            If Cells(x, 5).Value = "Yes" Then col = 28
            If Cells(x, 5).Value = "No" Then col = 29
            Cells(x, col).Value = Cells(x, 5).Value
        End If
    Next x
    
    Application.ScreenUpdating = True

End Sub

 

[Summer7sun]

 

[Summer7sun]

sorry abt the previous one

http://i64.tinypic.com/16bc1nq.jpg


this is the one .... when I run ur code it pastes to the blue area but i want like the image.... Thank you for trying to help me out

 

[JackDanIce]

Try:

Sub Update_Col2()
    
    Dim x   As Long
    Dim col As Long
    
    Application.ScreenUpdating = False
    
    For x = 8 To 108
        If Len(Cells(x, 20).Value) > 0 Then
            If Cells(x, 20).Value = "Yes" Then Cells(x, 28).Value = Cells(x, 5).Value
            If Cells(x, 20).Value = "No" Then Cells(x, 29).Value = Cells(x, 5).Value
        End If
    Next x
    
    Application.ScreenUpdating = True


End Sub

 

[Summer7sun]

this time it worked for 1 of the cells which had yes not the rest which had yes and no in T

http://i65.tinypic.com/2czsm04.jpg

 

[Summer7sun]

one more info eg if T11 is Yes then value of E11 wil b in AB11 (however next time the Value of T11 changes to NO then i don't want the previous value which was copied from E11 to AB11 to be removed)