If autofilter has results copy, if no results insert text VBA

[pook_666]

Hi wizards,

I have the below code that copies an autofilter to a different sheet.

 Sheets("Data").Range("A1").AutoFilter _
    Field:=11, Criteria1:="=Overdue"
    
    Sheets("Data").AutoFilter.Range.Copy Sheets("Debtors").Range("B17")

What I want to do is that if the autofilter produces no results, I want to put text in Sheets("Debtors").Range("B17") instead...something like Sheets("Debtors").Range("B17").value = "Nothing overdue" and if there is a result to this autofilter, then copy and paste as per above code.

I was thinking of an "IF isempty else" type of code but I don't think it's correct and cannot get this way to work....I know I am probably missing something simple with this, but any help would be great!!

 

[RoryA]

Something like:

With Sheets("Data").AutoFilter.Range
If .Columns(1).specialcells(xlcelltypevisible).count > 1 then 
   .Copy Sheets("Debtors").Range("B17")
Else
Sheets("Debtors").Range("B17").value = "Nothing overdue"
end if
end with

 

[pook_666]

Perfect - thank you!