Object does not support this property or method

Not sure if this is the problem, but I don't think any of your cells actually equal 0/1/1900. The Excel interpretation of the number zero in a cell formatted as date would be 0/1/1900 (or 1/0/1900 in U.S. English versions of Excel).

Perhaps the SQL field you're getting your data from is a date field, but it is empty and returning a 0 instead of a null value? I'm not an expert in SQL by any means, but it seems logical.

Try testing for 0 instead of 0/1/1900, like so..

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

UPDATE: Upon further review, you're using quotes around the first instance of Range("Sheet2!..."), but you don't use quotes for any of the other ranges. Try adding the quotes to each of the Range statements as shown in red above.

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....