About Us

keiji kounoike

Thank you for your feedback.

Keiji

Teddy wrote:
This is a very good macro. Thank you for stepping in and lending a hand. I
needed the help. I appreciate it very much. The macro works very well.
Thank you!

"keiji kounoike" <"kounoike A | T ma.Pik" wrote:

Try this one.

Sub FindPaste()
Dim FilterRng As Range
Dim CopyRng As Range, CopyRngF 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"
On Error Resume Next
Set CopyRngF = CopyRng.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not CopyRngF Is Nothing Then
CopyRng.Offset(0, 13).Resize _
(, 19).SpecialCells(xlCellTypeVisible).Copy _
Destination:=Sheets("Examine").Range("O1")
End If
Sheets("Statement").AutoFilterMode = False
Application.ScreenUpdating = True
End Sub

Keiji

Teddy wrote:
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 wont 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.Coun t, 31).Copy _
Destination:=Sheets("Examine").Range("O1")
End If

Sheets("Statement").AutoFilterMode = False
Application.ScreenUpdating = True
End Sub

.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Bypass Error Q	Sean	Excel Programming	2	October 17th 08 07:32 AM
How to bypass Excel error message	matelot	Excel Programming	2	August 28th 06 06:48 AM
How do I bypass the message to save when closing a file by macro?	[email protected]	Excel Programming	3	August 21st 06 09:01 PM
Bypass the "Macros may contain viruses . . ." message	Al	Excel Programming	5	October 21st 05 07:38 PM
debug message pops up when I select a range of cells and delete	Brian	Excel Programming	6	October 17th 04 12:55 AM

Menu

About Us