May 19 2020 09:29 AM - edited May 19 2020 09:41 AM
I need to help a user group with a spreadsheet. The person who made it left. Can someone assist with the following code? It fails on the line "PEntry.AutoFilter" with "AutoFilter method of Range class failed". A data entry form has a few fields, some buttons, with one a search button that directly runs the macro below. It was working fine with data entered. It quit working.
Sub Search()
'
' Search Macro
'
Sheets("Client Directory").Visible = True
Columns("G:J").Select
Selection.ClearContents
Range("G1").Value = "Possible Clients"
Dim First3 As Range, PEntry As Range
Set First3 = Sheets("Entry Form").Range("E6")
Set PEntry = Sheets("Client Directory").Columns("A:E")
Sheets("Client Directory").Select
Columns("A:E").Select
PEntry.AutoFilter Field:=5, Criteria1:=First3.Value
Sheets("Client Directory").Select
Columns("A:D").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Entry Form").Select
Range("G2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Client Directory").Visible = False
Range("E2:E3").Select
Selection.ClearContents
May 20 2020 03:41 AM
First thing first, the autofilter criteria is taken from EntryForm sheet E6 cell. You can manually copty the whole content of the cell (sometimes there may be some invisible chars there) and try a manuel filter on said range, Client Directory columns A:E. the filter condition applies to column E. If it does then we need to check other options