Sub FindPaste()

Application.ScreenUpdating = False
With Sheets("Statement")
With .Range("B3", .Range("B" & .Rows.Count).End(xlUp))
.Offset(-1).Resize(.Rows.Count + 1).AutoFilter _
Field:=1, Criteria1:="Agent"
On Error Resume Next
Sheets("Examine").Range("O1:AG1").Copy _
Destination:=.Offset(, 13).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
.AutoFilterMode = False
End With
Application.ScreenUpdating = True

End Sub


My goal is to create a macro that will look in Column B of worksheet
€œStatement€� ,starting in row 3, for the text €œAgent€�. When that text is
found I would like to copy that row Columns O:AG and then paste it into the
range O1:G1 of worksheet €œExamine.€�

The macro above will find the text €œAgent€� in Column B and then paste the
range from worksheet €œExamine€� into it, but I am trying to do the opposite.
I want to copy from random range based on text in worksheet €œStatement€�
Column O:AG and paste the data into specific range in worksheet €œExamine€�
O1:AG1. I gladly welcome any help. Thank you.

Hi

In the 'Copy' statement you have to switch CopyRng and Destination range.
The macro is changed as I would write it, but not tested:

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, 13).Resize _
(CopyRng.SpecialCells(xlCellTypeVisible).Rows.Coun t, 19).Copy _
Destination:=Sheets("Examine").Range("O1")
End If

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

Regards,
Per

"Teddy" skrev i meddelelsen
...
Sub FindPaste()

Application.ScreenUpdating = False
With Sheets("Statement")
With .Range("B3", .Range("B" & .Rows.Count).End(xlUp))
.Offset(-1).Resize(.Rows.Count + 1).AutoFilter _
Field:=1, Criteria1:="Agent"
On Error Resume Next
Sheets("Examine").Range("O1:AG1").Copy _
Destination:=.Offset(, 13).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
.AutoFilterMode = False
End With
Application.ScreenUpdating = True

End Sub


My goal is to create a macro that will look in Column B of worksheet
€œStatement€� ,starting in row 3, for the text €œAgent€�. When that text is
found I would like to copy that row Columns O:AG and then paste it into
the
range O1:G1 of worksheet €œExamine.€�

The macro above will find the text €œAgent€� in Column B and then paste the
range from worksheet €œExamine€� into it, but I am trying to do the
opposite.
I want to copy from random range based on text in worksheet €œStatement€�
Column O:AG and paste the data into specific range in worksheet €œExamine€�
O1:AG1. I gladly welcome any help. Thank you.

Sweet. Thank you. Very very close. Y:AG from "Statement" is getting pasted
into O1:Y1 of "Examine".

I am aiming for O:AG of "Statement" to get into O:AG of "Examine". Any
input is appreciated.

"Per Jessen" wrote:

Hi

In the 'Copy' statement you have to switch CopyRng and Destination range.
The macro is changed as I would write it, but not tested:

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, 13).Resize _
(CopyRng.SpecialCells(xlCellTypeVisible).Rows.Coun t, 19).Copy _
Destination:=Sheets("Examine").Range("O1")
End If

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

Regards,
Per

"Teddy" skrev i meddelelsen
...
Sub FindPaste()

Application.ScreenUpdating = False
With Sheets("Statement")
With .Range("B3", .Range("B" & .Rows.Count).End(xlUp))
.Offset(-1).Resize(.Rows.Count + 1).AutoFilter _
Field:=1, Criteria1:="Agent"
On Error Resume Next
Sheets("Examine").Range("O1:AG1").Copy _
Destination:=.Offset(, 13).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
.AutoFilterMode = False
End With
Application.ScreenUpdating = True

End Sub


My goal is to create a macro that will look in Column B of worksheet
€œStatement€� ,starting in row 3, for the text €œAgent€�. When that text is
found I would like to copy that row Columns O:AG and then paste it into
the
range O1:G1 of worksheet €œExamine.€�

The macro above will find the text €œAgent€� in Column B and then paste the
range from worksheet €œExamine€� into it, but I am trying to do the
opposite.
I want to copy from random range based on text in worksheet €œStatement€�
Column O:AG and paste the data into specific range in worksheet €œExamine€�
O1:AG1. I gladly welcome any help. Thank you.



.

It is working on my test data, do you by chance have any hidden columns,
when you run the macro?

---
Per

"Teddy" skrev i meddelelsen
...
Sweet. Thank you. Very very close. Y:AG from "Statement" is getting
pasted
into O1:Y1 of "Examine".

I am aiming for O:AG of "Statement" to get into O:AG of "Examine". Any
input is appreciated.

"Per Jessen" wrote:

Hi

In the 'Copy' statement you have to switch CopyRng and Destination range.
The macro is changed as I would write it, but not tested:

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, 13).Resize _
(CopyRng.SpecialCells(xlCellTypeVisible).Rows.Coun t, 19).Copy _
Destination:=Sheets("Examine").Range("O1")
End If

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

Regards,
Per

"Teddy" skrev i meddelelsen
...
Sub FindPaste()

Application.ScreenUpdating = False
With Sheets("Statement")
With .Range("B3", .Range("B" & .Rows.Count).End(xlUp))
.Offset(-1).Resize(.Rows.Count + 1).AutoFilter _
Field:=1, Criteria1:="Agent"
On Error Resume Next
Sheets("Examine").Range("O1:AG1").Copy _
Destination:=.Offset(,
13).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
.AutoFilterMode = False
End With
Application.ScreenUpdating = True

End Sub


My goal is to create a macro that will look in Column B of worksheet
€œStatement€� ,starting in row 3, for the text €œAgent€�. When that text
is
found I would like to copy that row Columns O:AG and then paste it into
the
range O1:G1 of worksheet €œExamine.€�

The macro above will find the text €œAgent€� in Column B and then paste
the
range from worksheet €œExamine€� into it, but I am trying to do the
opposite.
I want to copy from random range based on text in worksheet €œStatement€�
Column O:AG and paste the data into specific range in worksheet
€œExamine€�
O1:AG1. I gladly welcome any help. Thank you.



.

Awesome.

I changed

CopyRng.SpecialCells(xlCellTypeVisible).Offset(0, 13).Resize _
(CopyRng.SpecialCells(xlCellTypeVisible).Rows.Coun t, 19).Copy _

to

CopyRng.SpecialCells(xlCellTypeVisible).Offset(0, 3).Resize _
(CopyRng.SpecialCells(xlCellTypeVisible).Rows.Coun t, 31).Copy _

and got the result I was after. Thanks again for the help, I needed it very
much.

"Per Jessen" wrote:

It is working on my test data, do you by chance have any hidden columns,
when you run the macro?

---
Per

"Teddy" skrev i meddelelsen
...
Sweet. Thank you. Very very close. Y:AG from "Statement" is getting
pasted
into O1:Y1 of "Examine".

I am aiming for O:AG of "Statement" to get into O:AG of "Examine". Any
input is appreciated.

"Per Jessen" wrote:

Hi

In the 'Copy' statement you have to switch CopyRng and Destination range.
The macro is changed as I would write it, but not tested:

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, 13).Resize _
(CopyRng.SpecialCells(xlCellTypeVisible).Rows.Coun t, 19).Copy _
Destination:=Sheets("Examine").Range("O1")
End If

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

Regards,
Per

"Teddy" skrev i meddelelsen
...
Sub FindPaste()

Application.ScreenUpdating = False
With Sheets("Statement")
With .Range("B3", .Range("B" & .Rows.Count).End(xlUp))
.Offset(-1).Resize(.Rows.Count + 1).AutoFilter _
Field:=1, Criteria1:="Agent"
On Error Resume Next
Sheets("Examine").Range("O1:AG1").Copy _
Destination:=.Offset(,
13).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
.AutoFilterMode = False
End With
Application.ScreenUpdating = True

End Sub


My goal is to create a macro that will look in Column B of worksheet
€œStatement€� ,starting in row 3, for the text €œAgent€�. When that text
is
found I would like to copy that row Columns O:AG and then paste it into
the
range O1:G1 of worksheet €œExamine.€�

The macro above will find the text €œAgent€� in Column B and then paste
the
range from worksheet €œExamine€� into it, but I am trying to do the
opposite.
I want to copy from random range based on text in worksheet €œStatement€�
Column O:AG and paste the data into specific range in worksheet
€œExamine€�
O1:AG1. I gladly welcome any help. Thank you.



.

.

Thanks for your reply. I am glad you solved it and got it working.

--
Per

"Teddy" skrev i meddelelsen
...
Awesome.

I changed

CopyRng.SpecialCells(xlCellTypeVisible).Offset(0, 13).Resize _
(CopyRng.SpecialCells(xlCellTypeVisible).Rows.Coun t, 19).Copy _

to

CopyRng.SpecialCells(xlCellTypeVisible).Offset(0, 3).Resize _
(CopyRng.SpecialCells(xlCellTypeVisible).Rows.Coun t, 31).Copy _

and got the result I was after. Thanks again for the help, I needed it
very
much.

"Per Jessen" wrote:

It is working on my test data, do you by chance have any hidden columns,
when you run the macro?

---
Per

"Teddy" skrev i meddelelsen
...
Sweet. Thank you. Very very close. Y:AG from "Statement" is getting
pasted
into O1:Y1 of "Examine".

I am aiming for O:AG of "Statement" to get into O:AG of "Examine". Any
input is appreciated.

"Per Jessen" wrote:

Hi

In the 'Copy' statement you have to switch CopyRng and Destination
range.
The macro is changed as I would write it, but not tested:

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, 13).Resize _
(CopyRng.SpecialCells(xlCellTypeVisible).Rows.Coun t, 19).Copy
_
Destination:=Sheets("Examine").Range("O1")
End If

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

Regards,
Per

"Teddy" skrev i meddelelsen
...
Sub FindPaste()

Application.ScreenUpdating = False
With Sheets("Statement")
With .Range("B3", .Range("B" & .Rows.Count).End(xlUp))
.Offset(-1).Resize(.Rows.Count + 1).AutoFilter _
Field:=1, Criteria1:="Agent"
On Error Resume Next
Sheets("Examine").Range("O1:AG1").Copy _
Destination:=.Offset(,
13).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
.AutoFilterMode = False
End With
Application.ScreenUpdating = True

End Sub


My goal is to create a macro that will look in Column B of worksheet
€œStatement€� ,starting in row 3, for the text €œAgent€�. When that
text
is
found I would like to copy that row Columns O:AG and then paste it
into
the
range O1:G1 of worksheet €œExamine.€�

The macro above will find the text €œAgent€� in Column B and then
paste
the
range from worksheet €œExamine€� into it, but I am trying to do the
opposite.
I want to copy from random range based on text in worksheet
€œStatement€�
Column O:AG and paste the data into specific range in worksheet
€œExamine€�
O1:AG1. I gladly welcome any help. Thank you.



.

.