Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
copy rows to other sheet
Hi ,
I have a data with about 4500 rows and 12 columns. I would like to copy only the rows where column C haven't "0" value.can someone hep me with a macro ? Example Thank you |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
copy rows to other sheet
Hi,
Because you put 0 in quotes i'm not sure if you meant zero or the letter O so this assumes zero and copies sheet 1 to sheet 2. Alt+F11 to open VB editor insert a module and paste this in and try it:- Sub copyit() Dim MyRange, MyRange1 As Range Sheets("Sheet1").Select LastRow = Sheets("Sheet1").Range("C65536").End(xlUp).Row Set MyRange = Sheets("Sheet1").Range("C1:C" & LastRow) For Each C In MyRange If C.Value < 0 Then If MyRange1 Is Nothing Then Set MyRange1 = C.EntireRow Else Set MyRange1 = Union(MyRange1, C.EntireRow) End If End If Next MyRange1.Select Selection.Copy Sheets("Sheet2").Select Range("A1").Select ActiveSheet.Paste End Sub Mike "Carpe Diem" wrote: Hi , I have a data with about 4500 rows and 12 columns. I would like to copy only the rows where column C haven't "0" value.can someone hep me with a macro ? Example Thank you |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
copy rows to other sheet
Hi Mike,
It worked just like I need, however is it possible to verify to statements ? Column C < 0 (zero) and Column S <0 ? On Dec 17, 5:16 pm, Mike H wrote: Hi, Because you put 0 in quotes i'm not sure if you meant zero or the letter O so this assumes zero and copies sheet 1 to sheet 2. Alt+F11 to open VB editor insert a module and paste this in and try it:- Sub copyit() Dim MyRange, MyRange1 As Range Sheets("Sheet1").Select LastRow = Sheets("Sheet1").Range("C65536").End(xlUp).Row Set MyRange = Sheets("Sheet1").Range("C1:C" & LastRow) For Each C In MyRange If C.Value < 0 Then If MyRange1 Is Nothing Then Set MyRange1 = C.EntireRow Else Set MyRange1 = Union(MyRange1, C.EntireRow) End If End If Next MyRange1.Select Selection.Copy Sheets("Sheet2").Select Range("A1").Select ActiveSheet.Paste End Sub Mike "Carpe Diem" wrote: Hi , I have a data with about 4500 rows and 12 columns. I would like to copy only the rows where column C haven't "0" value.can someone hep me with a macro ? Example Thank you- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
copy rows to other sheet
maybe this
If c.Value < 0 And c.Offset(0, 16).Value < 0 Then Mike "Carpe Diem" wrote: Hi Mike, It worked just like I need, however is it possible to verify to statements ? Column C < 0 (zero) and Column S <0 ? On Dec 17, 5:16 pm, Mike H wrote: Hi, Because you put 0 in quotes i'm not sure if you meant zero or the letter O so this assumes zero and copies sheet 1 to sheet 2. Alt+F11 to open VB editor insert a module and paste this in and try it:- Sub copyit() Dim MyRange, MyRange1 As Range Sheets("Sheet1").Select LastRow = Sheets("Sheet1").Range("C65536").End(xlUp).Row Set MyRange = Sheets("Sheet1").Range("C1:C" & LastRow) For Each C In MyRange If C.Value < 0 Then If MyRange1 Is Nothing Then Set MyRange1 = C.EntireRow Else Set MyRange1 = Union(MyRange1, C.EntireRow) End If End If Next MyRange1.Select Selection.Copy Sheets("Sheet2").Select Range("A1").Select ActiveSheet.Paste End Sub Mike "Carpe Diem" wrote: Hi , I have a data with about 4500 rows and 12 columns. I would like to copy only the rows where column C haven't "0" value.can someone hep me with a macro ? Example Thank you- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search for rows in one sheet and copy into another sheet based on customer id | Excel Worksheet Functions | |||
Copy rows from one sheet to another based on a cell value | Excel Worksheet Functions | |||
what is the vb code to copy rows from one sheet to another? | Excel Discussion (Misc queries) | |||
Copy Rows to another Sheet Automatically | Excel Worksheet Functions | |||
Macro - to copy duplicate rows to another sheet | Excel Worksheet Functions |