VBA AutoFilter

[OX_2005]

Need a little help from the Excel Guru's out there.

I have a VBA AutoFilter set up and it works except for 1 small problem. If the auto filter returns with no data how can I with the code I have tell it after TBL.Range.AutoFilter Field:=12, Criteria1:="Term" to skip everything and start back after EE.Range("A2:L" & LR).SpecialCells(xlCellTypeVisible).Delete?
LR = ThisWorkbook.Worksheets("Employees").Cells(Rows.Count, 1).End(xlUp).Row
TBL.Range.AutoFilter Field:=12, Criteria1:="Term"
EE.Range("A2:L" & LR).Copy
TERM.Range("A1").End(xlDown).Offset(1).PasteSpecial (xlPasteValues)
Application.DisplayAlerts = False
EE.Range("A2:L" & LR).SpecialCells(xlCellTypeVisible).Delete
TBL.AutoFilter.ShowAllData

 

[Alex Blakenburg]

I think your reference to using TBL is using a ListObject but since you don't show what EE is I am a little confused.
See if this works for you:

    LR = ThisWorkbook.Worksheets("Employees").Cells(Rows.Count, 1).End(xlUp).Row
    TBL.Range.AutoFilter Field:=12, Criteria1:="Term"
    
    If TBL.Range.SpecialCells(xlCellTypeVisible).Rows.Address <> TBL.HeaderRowRange.Address Then
        EE.Range("A2:L" & LR).Copy
        TERM.Range("A1").End(xlDown).Offset(1).PasteSpecial (xlPasteValues)
        Application.DisplayAlerts = False
        EE.Range("A2:L" & LR).SpecialCells(xlCellTypeVisible).Delete
    End If
    TBL.AutoFilter.ShowAllData

 

[OX_2005]

I think your reference to using TBL is using a ListObject but since you don't show what EE is I am a little confused.
See if this works for you:

    LR = ThisWorkbook.Worksheets("Employees").Cells(Rows.Count, 1).End(xlUp).Row
    TBL.Range.AutoFilter Field:=12, Criteria1:="Term"
   
    If TBL.Range.SpecialCells(xlCellTypeVisible).Rows.Address <> TBL.HeaderRowRange.Address Then
        EE.Range("A2:L" & LR).Copy
        TERM.Range("A1").End(xlDown).Offset(1).PasteSpecial (xlPasteValues)
        Application.DisplayAlerts = False
        EE.Range("A2:L" & LR).SpecialCells(xlCellTypeVisible).Delete
    End If
    TBL.AutoFilter.ShowAllData

Sorry TBL = Set TBL = EE.ListObjects("Employees")

 

[OX_2005]

I know there should be an If and an End If but not sure what

Dim VT As Worksheet
Dim AT As Worksheet
Dim EE As Worksheet
Dim AB As Worksheet
Dim TERM As Worksheet
Dim LR As Long
Dim TBL As ListObject

Set VT = ThisWorkbook.Sheets("Vaca Tracker")
Set AT = ThisWorkbook.Sheets("Attend Tracker")
Set EE = ThisWorkbook.Sheets("Employees")
Set AB = ThisWorkbook.Sheets("Accrual Balances")
Set TERM = ThisWorkbook.Sheets("Term")
Set TBL = EE.ListObjects("Employees")


' Unprotecting Worksheets

VT.Unprotect
AT.Unprotect

' Not allowing screen to update

Application.ScreenUpdating = False

' Auto Filter Term & Delete

LR = ThisWorkbook.Worksheets("Employees").Cells(Rows.Count, 1).End(xlUp).Row
TBL.Range.AutoFilter Field:=12, Criteria1:="Term"
If
EE.Range("A2:L" & LR).Copy
TERM.Range("A1").End(xlDown).Offset(1).PasteSpecial (xlPasteValues)
Application.DisplayAlerts = False
EE.Range("A2:L" & LR).SpecialCells(xlCellTypeVisible).Delete
End If
TBL.AutoFilter.ShowAllData

 

[OX_2005]

Sorry TBL = Set TBL = EE.ListObjects("Employees")

That didnt work sorry

 

[Alex Blakenburg]

You don't seem to have used the If and End I provided. "Where" you have the If and End if fine but put my If line where you currently just have If.
If TBL.Range.SpecialCells(xlCellTypeVisible).Rows.Address <> TBL.HeaderRowRange.Address Then

PS: Please press the VBA code button and insert your code between the opening and closing code statements, it makes it much easier for others to read.

 

[OX_2005]

So I added the IF statement you provided and right now the doc has nothing in the table listed as "Term" so it should be skipping all the copy and paste and delete, but its continuing to go threw those steps.

Dim VT As Worksheet
Dim AT As Worksheet
Dim EE As Worksheet
Dim AB As Worksheet
Dim TERM As Worksheet
Dim LR As Long
Dim TBL As ListObject

Set VT = ThisWorkbook.Sheets("Vaca Tracker")
Set AT = ThisWorkbook.Sheets("Attend Tracker")
Set EE = ThisWorkbook.Sheets("Employees")
Set AB = ThisWorkbook.Sheets("Accrual Balances")
Set TERM = ThisWorkbook.Sheets("Term")
Set TBL = EE.ListObjects("Employees")


'   Unprotecting Worksheets

    VT.Unprotect
    AT.Unprotect

'   Not allowing screen to update

Application.ScreenUpdating = False

'   Auto Filter Term & Delete

LR = ThisWorkbook.Worksheets("Employees").Cells(Rows.Count, 1).End(xlUp).Row
TBL.Range.AutoFilter Field:=12, Criteria1:="Term"
   If TBL.Range.SpecialCells(xlCellTypeVisible).Rows.Address <> TBL.HeaderRowRange.Address Then
   EE.Range("A2:L" & LR).Copy
   TERM.Range("A1").End(xlDown).Offset(1).PasteSpecial (xlPasteValues)
   Application.DisplayAlerts = False
   EE.Range("A2:L" & LR).SpecialCells(xlCellTypeVisible).Delete
   End If
   TBL.AutoFilter.ShowAllData

 

[NoSparks]

Just out of curiosity, what does a message box say if you add one right after the filter

TBL.Range.AutoFilter Field:=12, Criteria1:="Term"
   
   MsgBox EE.AutoFilter.Range.Columns(12).SpecialCells(xlCellTypeVisible).Cells.Count - 1

 

[OX_2005]

It gives me a debug error

 

[OX_2005]

If I change EE to TBL is get a count back of how many there are weather it be 1 or 0

 MsgBox TBL.AutoFilter.Range.Columns(12).SpecialCells(xlCellTypeVisible).Cells.Count - 1