Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
summing across worksheets conditionally and being able to copy for | Excel Discussion (Misc queries) | |||
How do I conditionally format a row and copy the formatting to tab | Excel Worksheet Functions | |||
How can I have Excel conditionally copy a cell | Excel Worksheet Functions | |||
Conditionally formatting rows | Excel Worksheet Functions | |||
Hiding Rows Conditionally | Excel Discussion (Misc queries) |