AutoFilter method of Range Class Failed
I keep getting an error "AutoFilter method of Range Class Failed" when I run
this sub. I have data in B1:F232. Can someone help me, please ---------------------------------------------------------------------------- Sub test() Dim rng As Range Dim WS As Worksheet Dim WS2 As Worksheet Dim Str As String Dim LRow As Long Set WS = Sheets("Lod") Set WS2 = Sheets("Print282") LRow = LastRow(WS2) Str = "282" With WS.Columns("B:F") .AutoFilter Field:=1, Criteria1:=Str With WS.AutoFilter.Range On Error Resume Next Set rng = .Offset(0, 2).Resize(.Rows.Count - 1, 3) _ .SpecialCells(xlCellTypeVisible) On Error GoTo 0 If Not rng Is Nothing Then rng.Copy WS2.Range("B" & LRow + 1) End If End With End With WS.AutoFilterMode = False End Sub ---------------------------------------------------------------------------- Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ after:=sh.Range("B1"), _ lookat:=xlPart, _ LookIn:=xlValues, _ searchorder:=xlByRows, _ searchdirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function |
AutoFilter method of Range Class Failed
STR is a function in VBA.
Perhaps you should change your variable to Dim sStr as String and use that. Can't say it is your problem, but it may be. -- Regards, Tom Ogilvy "Randy Reese" wrote in message ink.net... I keep getting an error "AutoFilter method of Range Class Failed" when I run this sub. I have data in B1:F232. Can someone help me, please -------------------------------------------------------------------------- -- Sub test() Dim rng As Range Dim WS As Worksheet Dim WS2 As Worksheet Dim Str As String Dim LRow As Long Set WS = Sheets("Lod") Set WS2 = Sheets("Print282") LRow = LastRow(WS2) Str = "282" With WS.Columns("B:F") .AutoFilter Field:=1, Criteria1:=Str With WS.AutoFilter.Range On Error Resume Next Set rng = .Offset(0, 2).Resize(.Rows.Count - 1, 3) _ .SpecialCells(xlCellTypeVisible) On Error GoTo 0 If Not rng Is Nothing Then rng.Copy WS2.Range("B" & LRow + 1) End If End With End With WS.AutoFilterMode = False End Sub -------------------------------------------------------------------------- -- Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ after:=sh.Range("B1"), _ lookat:=xlPart, _ LookIn:=xlValues, _ searchorder:=xlByRows, _ searchdirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function |
AutoFilter method of Range Class Failed
Thanks Tom, but it didn't fix problem. :( Any further help would be
appreciated. "Tom Ogilvy" wrote in message ... STR is a function in VBA. Perhaps you should change your variable to Dim sStr as String and use that. Can't say it is your problem, but it may be. -- Regards, Tom Ogilvy "Randy Reese" wrote in message ink.net... I keep getting an error "AutoFilter method of Range Class Failed" when I run this sub. I have data in B1:F232. Can someone help me, please -------------------------------------------------------------------------- -- Sub test() Dim rng As Range Dim WS As Worksheet Dim WS2 As Worksheet Dim Str As String Dim LRow As Long Set WS = Sheets("Lod") Set WS2 = Sheets("Print282") LRow = LastRow(WS2) Str = "282" With WS.Columns("B:F") .AutoFilter Field:=1, Criteria1:=Str With WS.AutoFilter.Range On Error Resume Next Set rng = .Offset(0, 2).Resize(.Rows.Count - 1, 3) _ .SpecialCells(xlCellTypeVisible) On Error GoTo 0 If Not rng Is Nothing Then rng.Copy WS2.Range("B" & LRow + 1) End If End With End With WS.AutoFilterMode = False End Sub -------------------------------------------------------------------------- -- Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ after:=sh.Range("B1"), _ lookat:=xlPart, _ LookIn:=xlValues, _ searchorder:=xlByRows, _ searchdirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function |
AutoFilter method of Range Class Failed
Fixed, it was the .takefocusonclick property.
Thanks Tom. "Randy Reese" wrote in message nk.net... Thanks Tom, but it didn't fix problem. :( Any further help would be appreciated. "Tom Ogilvy" wrote in message ... STR is a function in VBA. Perhaps you should change your variable to Dim sStr as String and use that. Can't say it is your problem, but it may be. -- Regards, Tom Ogilvy "Randy Reese" wrote in message ink.net... I keep getting an error "AutoFilter method of Range Class Failed" when I run this sub. I have data in B1:F232. Can someone help me, please -------------------------------------------------------------------------- -- Sub test() Dim rng As Range Dim WS As Worksheet Dim WS2 As Worksheet Dim Str As String Dim LRow As Long Set WS = Sheets("Lod") Set WS2 = Sheets("Print282") LRow = LastRow(WS2) Str = "282" With WS.Columns("B:F") .AutoFilter Field:=1, Criteria1:=Str With WS.AutoFilter.Range On Error Resume Next Set rng = .Offset(0, 2).Resize(.Rows.Count - 1, 3) _ .SpecialCells(xlCellTypeVisible) On Error GoTo 0 If Not rng Is Nothing Then rng.Copy WS2.Range("B" & LRow + 1) End If End With End With WS.AutoFilterMode = False End Sub -------------------------------------------------------------------------- -- Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ after:=sh.Range("B1"), _ lookat:=xlPart, _ LookIn:=xlValues, _ searchorder:=xlByRows, _ searchdirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function |
All times are GMT +1. The time now is 08:34 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com