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

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

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

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