Dear Excel Experts,

I've been trying to find solution for this for almost a week but to no avail. Just really don't understand which part I've done wrong.

My code below should do copy to filtered data and paste them below the row (skip one blank row to differ) of the original data. The original data is in csv format which located in separate workbook. It has a checking command line to ensure that the filtered data is not blank, else skip and run next sub macro. This is needed to run through thousands of original data in separate workbooks. The weird thing that happens is that some files, the macro detects blank correctly, some won't and will prompt the error and stops the macro. I tried to change between count and countA for detecting the filtered data rows but if i do that the result will also be different, as if some of it is detected as text so, it will count in CountA but some will only be counted if using Count.. I have tried different version of Excel, ranging from 2010 up to 2016 but no difference.. I have tried many different macro commands to count only visible data rows but still don't give any difference.

This has been so much pain for me as I need to get accurate data to calculate SLA performance of each staff in attending tickets. Please, please, please help me.. 


For Each Sheet In ActiveWorkbook.Sheets
'Call tukardate
Columns("D:E").NumberFormat = "@"
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
    Selection.AutoFilter
    Range("A1").Select
    'LRow = Range("A" & Rows.Count).End(xlUp).Row
    LRow = Range("A1", Range("A1").End(xlDown)).Rows.Count
    'MsgBox LRow
    Set r = ActiveSheet.Range("$A$1:$F$" & LRow)
    r.AutoFilter Field:=5, Criteria1:=Array("PREPAID_SUPPORT", "28882"), Operator:=xlFilterValues
    
    'r.AutoFilter Field:=4, Criteria1:=Array( _
        "V03658X", "V01553X", "V03477X", "V03751X", "V03752X", "V04046X", "V04047X"), Operator:=xlFilterValues
    
    
    'LR = Range("A" & Rows.Count).End(.xlUp).Row
    'Range("A2:P" & LR).SpecialCells(xlCellTypeVisible).Select

    'jcell = WorksheetFunction.CountA(r.Cells.SpecialCells(xlCellTypeVisible))
    jcell = WorksheetFunction.CountA(ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible))
    'MsgBox jcell
    'wcell = WorksheetFunction.Count(r.Cells.SpecialCells(xlCellTypeVisible))
    wcell = WorksheetFunction.Count(ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible))
    'MsgBox wcell
    'MsgBox jcell
    If jcell = 0 Or wcell = 0 Then
    'If wcell = 0 Then
    If ThisWorkbook.Worksheets("Macros").Range("M1").Value = "" Then ThisWorkbook.Worksheets("Macros").Range("M1") = "1st Filter To Re-export:"
    MyRow = ThisWorkbook.Worksheets("Macros").Range("M" & Rows.Count).End(xlUp).Row
    'MsgBox MyRow
    ThisWorkbook.Worksheets("Macros").Range("M" & (MyRow + 1)).Value = FileName
    MyRow = ThisWorkbook.Worksheets("Macros").Range("M" & Rows.Count).End(xlUp).Row
    ThisWorkbook.Worksheets("Macros").Range("$M$1:$M$" & MyRow).RemoveDuplicates Columns:=1, Header:=xlYes
'End If
    GoTo OtherFilter
    
    End If
    Range("A2:F" & LRow).SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Rows((LRow + 2) & ":" & (LRow + 2)).Select
    ActiveSheet.Paste
    
    Selection.AutoFilter

GoTo OtherFilter

OtherFilter:
    'Range("D1").Select
    'Selection.AutoFilter
    
    Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
    Selection.AutoFilter
    Range("A1").Select
    'LRow = Range("A" & Rows.Count).End(xlUp).Row
    LRow = Range("A1", Range("A1").End(xlDown)).Rows.Count
    'LRow = LRow - 2
    'MsgBox LRow
    Set r = ActiveSheet.Range("$A$1:$F$" & LRow)
    r.AutoFilter Field:=4, Criteria1:=Array( _
        "V03658X", "V01553X", "V03477X", "V03751X", "V03752X", "V04046X", "V04047X"), Operator:=xlFilterValues
    'r.AutoFilter Field:=5, Criteria1:=Array("PREPAID_SUPPORT", "28882"), Operator:=xlFilterValues
    
    'r.AutoFilter Field:=4, Criteria1:="V04047X", Operator:=xlFilterValues
    'kcell = WorksheetFunction.CountA(r.Cells.SpecialCells(xlCellTypeVisible))
    kcell = WorksheetFunction.CountA(ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible))
    'MsgBox kcell
    'vcell = WorksheetFunction.Count(r.Cells.SpecialCells(xlCellTypeVisible))
    wcell = WorksheetFunction.Count(ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible))

    'MsgBox vcell
    'MsgBox kcell
    If kcell = 0 Or vcell = 0 Then
    'If vcell = 0 Then
    If ThisWorkbook.Worksheets("Macros").Range("N1").Value = "" Then ThisWorkbook.Worksheets("Macros").Range("N1") = "2nd Filter to Re-export:"
    MyRow1 = ThisWorkbook.Worksheets("Macros").Range("N" & Rows.Count).End(xlUp).Row
    'MsgBox MyRow1
    ThisWorkbook.Worksheets("Macros").Range("N" & (MyRow1 + 1)).Value = FileName
    MyRow1 = ThisWorkbook.Worksheets("Macros").Range("N" & Rows.Count).End(xlUp).Row
    ThisWorkbook.Worksheets("Macros").Range("$N$1:$N$" & MyRow1).RemoveDuplicates Columns:=1, Header:=xlYes
    GoTo OtherWsheet
    End If
    
    'Range("A" & LRow + 1).Select
    

    'cnt = Range("A" & (LRow + 2)).CurrentRegion.Rows.Count
    'MsgBox cnt
    'MsgBox LRow
    'nxtr = LRow + 1
    'MsgBox nxtr
    'LRow = LRow - 2
    Range("A2:F" & LRow).SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    'cnt = LRow + 3
    LRow = Range("A" & Rows.Count).End(xlUp).Row
    'MsgBox cnt
    Rows(LRow + 1 & ":" & LRow + 1).Select
    'Rows(nxtr & ":" & nxtr).Select
    ActiveSheet.Paste
GoTo OtherWsheet

OtherWsheet:
'MsgBox LRow
   Range("A1").Select
   Selection.AutoFilter
   LRow = Range("A1", Range("A1").End(xlDown)).Rows.Count
   'MsgBox LRow
  'LRow = Range("A" & Rows.Count).End(xlUp).Row
  'If Range("A" & LRow).Value = "" Then Rows("2:" & LRow).Delete Else Rows("2:" & (LRow + 1)).Delete
  Rows("2:" & LRow + 1).Delete
   ' Range("A2").EntireRow.Delete
    'Rows("2:2").Select
   ' Range(Selection, Selection.End(xlDown)).Delete
   ' Rows("2:2").Delete
   LRow = Range("A1", Range("A1").End(xlDown)).Rows.Count
    ActiveSheet.Range("$A$1:$F$" & LRow).RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6), Header:=xlYes
    LRow = Range("A" & Rows.Count).End(xlUp).Row
    Range("A1").Select