I think i have it sussed. I just amended date to the previous and seems to be picking up the data.
Code:
Jan = #1/1/2012#
Feb = #1/2/2012#
My last problem is that my boss has decided he wants to filter by ETA not ETS so I need to change to column. I tried to do this by changing Ets_Col to Eta_Col and amending the code but its not liking it.
Code:
Dim wksRawData As Worksheet
    Dim wksHKKot As Worksheet
    Dim wksHKHam As Worksheet
    Dim wksXMNSHAHam As Worksheet
    Dim rngRawData As Range
    Dim lngCount As Long
    
    Dim Jan As Date
    Dim Feb As Date
    Dim HK As String
    Dim KT As String
    Dim HAM As String
    Dim XMN As String
    Dim SHA As String
    
    Dim LastRow As Long
    Dim Eta_Col As String
    Dim Pol_Col As String
    Dim PoD_Col As String
    
    Set wksRawData = Worksheets("Raw Data")
    Set wksHKKot = Worksheets("HKG to Kotka")
    Set wksHKHam = Worksheets("HKG to Hamburg")
    Set wksXMNSHAHam = Worksheets("XMN | SHA to Hamburg")
    
    Jan = #1/1/2012#
    Feb = #1/2/2012#
    HK = "Hong Kong"
    KT = "Kotka"
    HAM = "Hamburg"
    XMN = "Xiamen"
    SHA = "Shanghai"
    
    With wksRawData
        If .AutoFilterMode Then .AutoFilterMode = False 'remove autofilter
        LastRow = .Range("l" & .Rows.Count).End(xlUp).Row
        Set rngRawData = .Range("a5:w" & LastRow) 'set the range
        Eta_Col = "'" & .Name & "'!L6:L" & LastRow
        Pol_Col = "'" & .Name & "'!n6:n" & LastRow
        PoD_Col = "'" & .Name & "'!o6:o" & LastRow
    End With
    
    ' **************** Hong Kong to Kotka ***********************************
    'count whether any shipment exist in January from Hong Kong to Kotka
    lngCount = Evaluate("sumproduct(--(month(" & Eta_Col & ")=month(""" & Jan & """+0)),--(" & Pol_Col & "=""" & HK & """),--(" & PoD_Col & "=""" & KT & """))")
problem ocurres on the last line of above code
Thanks so much