don't copy filtered data if no active cells

[xander1981]

Thanks Admin, I tweeked a couple of things and now works brilliant. second filter criteria was > Feb and should have been < Feb Thanks for All you help. Final code that worked:=

Sub Jan()
'*********************** January KPI Report **************************

    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
    
    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
        Set rngRawData = .Range("a5:w" & .Range("k" & .Rows.Count).End(xlUp).Row) 'set the range
    End With
    
    ' **************** Hong Kong to Kotka ***********************************
    With Application.WorksheetFunction
        'count whether any shipment exist in January from Hong Kong to Kotka
        lngCount = .CountIf(wksRawData.Columns(11), ">=" & Jan) - .CountIf(wksRawData.Columns(11), ">" & Feb)
        lngCount = lngCount * .CountIf(wksRawData.Columns(14), "=" & HK)
        lngCount = lngCount * .CountIf(wksRawData.Columns(15), "=" & KT)
    End With
    
    'if exist
    If lngCount Then
        With rngRawData
            .AutoFilter field:=11, Criteria1:=">=" & Jan, Operator:=xlAnd, _
                    Criteria2:="<" & Feb, Operator:=xlAnd
            .AutoFilter field:=14, Criteria1:=HK
            .AutoFilter field:=15, Criteria1:=KT
            Set rng = .Cells(1).Offset(1).Resize(.Rows.Count - 1, .Columns.Count).SpecialCells(12)
        End With
        wksHKKot.Range("A11:W40").ClearContents
        rng.Copy wksHKKot.Range("A11")
        wksRawData.ShowAllData
        Else
        MsgBox "No Shipments from Hong Kong to Kotka in January"
    End If
  ' *************************************************************************
     
 ' **************** Hong Kong to Hamburg ***********************************
    'reset lngCount
    lngCount = 0
    With Application.WorksheetFunction
        'count whether any shipment exist in January from Hong Kong to Hamburg
        lngCount = .CountIf(wksRawData.Columns(11), ">=" & Jan) - .CountIf(wksRawData.Columns(11), ">" & Feb)
        lngCount = lngCount * .CountIf(wksRawData.Columns(14), "=" & HK)
        lngCount = lngCount * .CountIf(wksRawData.Columns(15), "=" & HAM)
    End With
    
    'if exist
    If lngCount Then
        With rngRawData
            .AutoFilter field:=11, Criteria1:=">=" & Jan, Operator:=xlAnd, _
                    Criteria2:="<" & Feb, Operator:=xlAnd
            .AutoFilter field:=14, Criteria1:=HK
            .AutoFilter field:=15, Criteria1:=HAM
            Set rng = .Cells(1).Offset(1).Resize(.Rows.Count - 1, .Columns.Count).SpecialCells(12)
        End With
        wksHKHam.Range("A11:W40").ClearContents
        rng.Copy wksHKHam.Range("A11")
        wksRawData.ShowAllData
        Else
        MsgBox "No Shipments from Hong Kong to Hamburg in January"
    End If
  ' *************************************************************************
  
  ' **************** Xiamen | Shanghai to Hamburg ***********************************
    'reset lngCount
    lngCount = 0
    With Application.WorksheetFunction
        'count whether any shipment exist in January from Xiamen and Shanghai to Hamburg
        lngCount = .CountIf(wksRawData.Columns(11), ">=" & Jan) - .CountIf(wksRawData.Columns(11), "<" & Feb)
        lngCount = lngCount * .CountIf(wksRawData.Columns(14), "=" & SHA)
        lngCount = lngCount * .CountIf(wksRawData.Columns(14), "=" & XMN)
        lngCount = lngCount * .CountIf(wksRawData.Columns(15), "=" & HAM)
    End With
    
    'if exist
    If lngCount Then
        With rngRawData
            .AutoFilter field:=11, Criteria1:=">=" & Jan, Operator:=xlAnd, _
                    Criteria2:="<" & Feb, Operator:=xlAnd
            .AutoFilter field:=14, Criteria1:=SHA, Operator:=xlOr, _
            Criteria2:=XMN
            .AutoFilter field:=15, Criteria1:=HAM
            Set rng = .Cells(1).Offset(1).Resize(.Rows.Count - 1, .Columns.Count).SpecialCells(12)
        End With
        wksXMNSHAHam.Range("A11:W40").ClearContents
        rng.Copy wksXMNSHAHam.Range("A11")
        wksRawData.ShowAllData
        Else
        MsgBox "No Shipments from Hong Kong to Hamburg in January"
    End If
    
  ' *************************************************************************
     
    Set wksRawData = Nothing
    Set wksHKKot = Nothing
    Set wksHKHam = Nothing
    Set wksXMNSHAHam = Nothing
    
End Sub