T
Teddy
This macro will search Column B3:B of sheet ‘Statement’ for text ‘Agent’.
When found it copies that row of Columns O:AG and pastes the data into sheet
‘Examine’ range O1:AG1. Sometimes the text ‘Agent’ is not in Column B, when
that happens I get an error message to debug the macro. Do you know what I
can do to this macro so that I won’t get a message to debug even when the
text ‘Agent’ cannot be found in Column B?
Sub FindPaste()
Dim FilterRng As Range
Dim CopyRng As Range
Application.ScreenUpdating = False
With Sheets("Statement")
Set FilterRng = .Range("B2", .Range("B" & Rows.Count).End(xlUp))
Set CopyRng = .Range("B3", .Range("B" & Rows.Count).End(xlUp))
End With
FilterRng.AutoFilter Field:=1, Criteria1:="Agent"
If CopyRng.SpecialCells(xlCellTypeVisible).Rows.Count > 0 Then
CopyRng.SpecialCells(xlCellTypeVisible).Offset(0, 3).Resize _
(CopyRng.SpecialCells(xlCellTypeVisible).Rows.Count, 31).Copy _
Destination:=Sheets("Examine").Range("O1")
End If
Sheets("Statement").AutoFilterMode = False
Application.ScreenUpdating = True
End Sub
When found it copies that row of Columns O:AG and pastes the data into sheet
‘Examine’ range O1:AG1. Sometimes the text ‘Agent’ is not in Column B, when
that happens I get an error message to debug the macro. Do you know what I
can do to this macro so that I won’t get a message to debug even when the
text ‘Agent’ cannot be found in Column B?
Sub FindPaste()
Dim FilterRng As Range
Dim CopyRng As Range
Application.ScreenUpdating = False
With Sheets("Statement")
Set FilterRng = .Range("B2", .Range("B" & Rows.Count).End(xlUp))
Set CopyRng = .Range("B3", .Range("B" & Rows.Count).End(xlUp))
End With
FilterRng.AutoFilter Field:=1, Criteria1:="Agent"
If CopyRng.SpecialCells(xlCellTypeVisible).Rows.Count > 0 Then
CopyRng.SpecialCells(xlCellTypeVisible).Offset(0, 3).Resize _
(CopyRng.SpecialCells(xlCellTypeVisible).Rows.Count, 31).Copy _
Destination:=Sheets("Examine").Range("O1")
End If
Sheets("Statement").AutoFilterMode = False
Application.ScreenUpdating = True
End Sub