Cell value based on another cell if text in there's specific text in another cell

[carlomscalisi]

Hopefully the title wasn't too confusing.

I have a workbook with two sheets. On one sheet I have a column for comments and an adjacent column with a list value of "Y" or "N". Then I have another sheet and on that sheet I would like to have a column that contains the comments from the other sheet IF the adjacent column contains "Y". I hope that makes sense. Please help!


Thank you!

 

[carlomscalisi]

Try this macro. It will prompt you to enter the name of the reference sheet.

Sub CopyData()
    Application.ScreenUpdating = False
    Dim LastRow As Long, srcWS As Worksheet, desWS As Worksheet, response As String
    response = InputBox("Please enter the name of the reference sheet.")
    If response = "" Then Exit Sub
    Set srcWS = Sheets(response)
    Set desWS = Sheets("Follow-up")
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With srcWS
        .Range("M1:M" & LastRow).AutoFilter Field:=1, Criteria1:="Y"
        .Range("A2:A" & LastRow).SpecialCells(xlCellTypeVisible).Copy desWS.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        .Range("L2:L" & LastRow).SpecialCells(xlCellTypeVisible).Copy desWS.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
        .Range("M1").AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub

Is there a way to only copy the values, and not the formatting of the other cells?

 

[mumps]

Try:

Sub CopyData()
    Application.ScreenUpdating = False
    Dim LastRow As Long, srcWS As Worksheet, desWS As Worksheet, response As String
    response = InputBox("Please enter the name of the reference sheet.")
    If response = "" Then Exit Sub
    Set srcWS = Sheets(response)
    Set desWS = Sheets("Follow-up")
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With srcWS
        .Range("M1:M" & LastRow).AutoFilter Field:=1, Criteria1:="Y"
        .Range("A2:A" & LastRow).SpecialCells(xlCellTypeVisible).Copy
        desWS.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        .Range("L2:L" & LastRow).SpecialCells(xlCellTypeVisible).Copy
        desWS.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        Application.CutCopyMode = False
        .Range("M1").AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub