copy rows conditionally
 

Hi,
The below code, which copies a region from Sheet1 to Sheet4 works with no
trouble at all...
But I need to modify it so that only the rows having an "X" on column A will
be copied.

Can anyone suggest an alteration?

'-------------------------
Sub Macro3()
' Macro3 Macro
Sheets("Sheet1").Activate
Range("A5:F400").Select
Selection.Copy
Range("A1").Select

Sheets("Sheet4").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A1:F400").Select

Selection.Sort Key1:=Range("A2"), Order1:=xlDescending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Range("A1").Select
End Sub
'----------------------------
TIA
J_J

copy rows conditionally
 

This can all be done without SELECTING cells or ranges which slows things down.
Also if you filter the source list on col A before copying, you will only
copy the rows which have an x in col A. So this works for me;

Sub CopyFilteredList()

Dim Source As Range
Dim Dest As Range

Set Source = Sheets("Sheet1").Range("A5:F400")
Set Dest = Sheets("Sheet4").Range("A1")

Source.AutoFilter Field:=1, Criteria1:="x"
Source.Copy Dest 'copy to destination
Application.Goto Dest
Dest.CurrentRegion.Sort Key1:=Range("B1"), Order1:=xlDescending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
'reset source to show all records
Source.AutoFilter

End Sub

"J_J" wrote:

Hi,
The below code, which copies a region from Sheet1 to Sheet4 works with no
trouble at all...
But I need to modify it so that only the rows having an "X" on column A will
be copied.

Can anyone suggest an alteration?

'-------------------------
Sub Macro3()
' Macro3 Macro
Sheets("Sheet1").Activate
Range("A5:F400").Select
Selection.Copy
Range("A1").Select

Sheets("Sheet4").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A1:F400").Select

Selection.Sort Key1:=Range("A2"), Order1:=xlDescending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Range("A1").Select
End Sub
'----------------------------
TIA
J_J

copy rows conditionally
 

It workes here too...thank you gocush.
J_J

"gocush" /delete wrote in message
...
This can all be done without SELECTING cells or ranges which slows things
down.
Also if you filter the source list on col A before copying, you will only
copy the rows which have an x in col A. So this works for me;

Sub CopyFilteredList()

Dim Source As Range
Dim Dest As Range

Set Source = Sheets("Sheet1").Range("A5:F400")
Set Dest = Sheets("Sheet4").Range("A1")

Source.AutoFilter Field:=1, Criteria1:="x"
Source.Copy Dest 'copy to destination
Application.Goto Dest
Dest.CurrentRegion.Sort Key1:=Range("B1"), Order1:=xlDescending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
'reset source to show all records
Source.AutoFilter

End Sub

"J_J" wrote:

Hi,
The below code, which copies a region from Sheet1 to Sheet4 works with no
trouble at all...
But I need to modify it so that only the rows having an "X" on column A
will
be copied.

Can anyone suggest an alteration?

'-------------------------
Sub Macro3()
' Macro3 Macro
Sheets("Sheet1").Activate
Range("A5:F400").Select
Selection.Copy
Range("A1").Select

Sheets("Sheet4").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A1:F400").Select

Selection.Sort Key1:=Range("A2"), Order1:=xlDescending,
Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Range("A1").Select
End Sub
'----------------------------
TIA
J_J