Prev: Converting MS query from Excel 2003 to Excel 2007
Next: Apply format to >0 cells
From: keiji kounoike "kounoike A | T on 7 Mar 2010 19:43
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 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
>>>
>>>
>> .
>>
First  |  Prev  | 
Pages: 1 2
Prev: Converting MS query from Excel 2003 to Excel 2007
Next: Apply format to >0 cells