Abord sub if criteria is not met

[AlexSrois]

Hi guys! Need your help!

I've got a macro that :
1) Select all the rows from a table when the A value is ''Complété''
2) Copy these rows to another worksheet
3) Delete theses rows from the first table

My sub is working perfectly, execepted that if there is no value that meets the criteria (''complété''), then it deletes everything. I'm pretty sure you can see why this is a problem

How can I make the sub to abord itself if the criteria isn't met?

Here is my sub ;

Sub Archive()

' Copy Completed task value from WorksheetPanier to WorksheetArchives

Application.ScreenUpdating = False
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet

    Sheets("Panier").Select
   ActiveSheet.ListObjects("Panier").Range.AutoFilter Field:=1, Criteria1:= _
        "Complété"
    Range("Panier").Select
    Selection.Copy
  
  Set copySheet = Worksheets("Panier")
  Set pasteSheet = Worksheets("Archives")

  pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
  Application.CutCopyMode = False
  Application.ScreenUpdating = True
  
' Go back and delete Completed task
  
    Sheets("Panier").Select
    Application.CutCopyMode = False
    Selection.EntireRow.Delete
    ActiveSheet.ListObjects("Panier").Range.AutoFilter Field:=1
     
' End routine

    ThisWorkbook.Save
    ActiveWorkbook.RefreshAll
    
End Sub

 

[igold]

How about if you add this one line of code where indicated:

Sub Archive()

' Copy Completed task value from WorksheetPanier to WorksheetArchives

Application.ScreenUpdating = False
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet

    Sheets("Panier").Select
   ActiveSheet.ListObjects("Panier").Range.AutoFilter Field:=1, Criteria1:= _
        "Complété"
    Range("Panier").Select
    If Selection.Offset(1, 0).SpecialCells(xlCellTypeVisible).Cells.Count) = ActiveSheet.ListObjects("Panier").ListColumns.Count Then Exit Sub '**** ADD THIS LINE ***
    Selection.Copy
  
  Set copySheet = Worksheets("Panier")
  Set pasteSheet = Worksheets("Archives")

  pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
  Application.CutCopyMode = False
  Application.ScreenUpdating = True
  
' Go back and delete Completed task
  
    Sheets("Panier").Select
    Application.CutCopyMode = False
    Selection.EntireRow.Delete
    ActiveSheet.ListObjects("Panier").Range.AutoFilter Field:=1
     
' End routine

    ThisWorkbook.Save
    ActiveWorkbook.RefreshAll
    
End Sub

 

[AlexSrois]

How about if you add this one line of code where indicated:

Sub Archive()

' Copy Completed task value from WorksheetPanier to WorksheetArchives

Application.ScreenUpdating = False
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet

    Sheets("Panier").Select
   ActiveSheet.ListObjects("Panier").Range.AutoFilter Field:=1, Criteria1:= _
        "Complété"
    Range("Panier").Select
    If Selection.Offset(1, 0).SpecialCells(xlCellTypeVisible).Cells.Count) = ActiveSheet.ListObjects("Panier").ListColumns.Count Then Exit Sub '**** ADD THIS LINE ***
    Selection.Copy
 
  Set copySheet = Worksheets("Panier")
  Set pasteSheet = Worksheets("Archives")

  pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
  Application.CutCopyMode = False
  Application.ScreenUpdating = True
 
' Go back and delete Completed task
 
    Sheets("Panier").Select
    Application.CutCopyMode = False
    Selection.EntireRow.Delete
    ActiveSheet.ListObjects("Panier").Range.AutoFilter Field:=1
   
' End routine

    ThisWorkbook.Save
    ActiveWorkbook.RefreshAll
  
End Sub

Thought it was working, but that doesn't copy then erase anymore ; it just prevent from error delete

 

[igold]

You're welcome. I was happy to help. Thanks for the feedback.

 

[AlexSrois]

Thought it was working, but that doesn't copy then erase anymore ; it just prevent from error delete

Fixed! Added an "Else" at the end.
Thank you so much for your formula.
Anyway I can erase the filter before exiting sub?

 

[igold]

In the appropriate spot in your code try this...

 ActiveSheet.ListObjects("Panier").AutoFilter.ShowAllData

 

[AlexSrois]

In the appropriate spot in your code try this...

 ActiveSheet.ListObjects("Panier").AutoFilter.ShowAllData

I mean, after the If statement ;
Like

If Selection.Offset(1, 0).SpecialCells(xlCellTypeVisible).Cells.Count = ActiveSheet.ListObjects("Panier").ListColumns.Count Then ActiveSheet.ListObjects("Panier").AutoFilter.ShowAllData Then Exit Sub Else
    Selection.Copy

That would not work, but you get what I'm trying to do

 

[igold]

You would need to make it an if/then multiple lines - Perhaps this...

If Selection.Offset(1, 0).SpecialCells(xlCellTypeVisible).Cells.Count = ActiveSheet.ListObjects("Panier").ListColumns.Count Then
     ActiveSheet.ListObjects("Panier").AutoFilter.ShowAllData
     Exit Sub
End if

 

[igold]

I did not account for the fact that you want to the sub to do other stuff, but I think you had that figured out already....

 

[AlexSrois]

You would need to make it an if/then multiple lines - Perhaps this...

If Selection.Offset(1, 0).SpecialCells(xlCellTypeVisible).Cells.Count = ActiveSheet.ListObjects("Panier").ListColumns.Count Then
     ActiveSheet.ListObjects("Panier").AutoFilter.ShowAllData
     Exit Sub
End if

Great, everything seems to be working fine! Thank a lot for your time and patience! :D