Object does not support this property or method

Sheesh. Upon even further review, I'd recommend using the following code format:

Sub Populate_Cells()
    With Sheets("Sheet2")
        If .Range("CW6") <> 0 Then
            .Range("N6").Value = .Range("CW6")
        ElseIf .Range("CS6") <> 0 Then
            .Range("N6").Value = .Range("CS6")
        ElseIf .Range("AK6") <> 0 Then
            .Range("N6").Value = .Range("AK6")
        ElseIf .Range("AG6") <> 0 Then
            .Range("N6").Value = .Range("AG6")
        Else
            .Range("N6").Value = .Range("AG6")
        End If
    End With
End Sub

Instead of using Range("Sheet2!CW6"), explicity define the Sheet name. You only have to type it once when using a With/End With block, and .'s before each Range (otherwise you'd have to constantly type Sheets("Sheet2").Range("N6").Value....