Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cut rows if contains "Done" from sheet1, and paste onto next available row in sheet2 in the same workbook.
Hi, all.
I faced VBA issue. IF you can spare me some time to do this project, it would be super nice. On sheet1, I will select entire row by clicking row number on the left, and I wisg I have a macro to send this selected row to next available row in sheet2. I used something like this, but doesn't help. Private Sub to1() Selection.Cut Sheets("1").Range("b65000").End(xlUp).Offset(1, 0).Value = Sheets("Prioritization").Selection End Sub and I also used this. Sub CutData() Dim sStr As String, Cell As Range, rng As Range sStr = "#Done#" For Each Cell In Range("n7:n900") If InStr(1, sStr, Cell.Value, vbTextCompare) 0 Then If rng Is Nothing Then Set rng = Cell Else Set rng = Union(rng, Cell) End If End If Next If Not rng Is Nothing Then rng.EntireRow.Copy Destination:=Worksheets( _ "OtherSheet").Range("A1") rng.EntireRow.Delete End If End Sub But you know what? If I can have a macro actually sends rows contains "done" from sheet1 to sheet2. it would be super nice. Once the row has "done" in it, "done" will be located in "N" column. Thank you again. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cut rows if contains "Done" from sheet1, and paste onto next available row in sheet2 in the same workbook.
here's a very basic concept.
it will copy any row that has done in column n from 7 to 900 then copy it to sheet2 starting in row 4 then delete the row on sheet1 adapt to your needs Sub move_rows() Dim i As Integer i = 4 Application.ScreenUpdating = False With Worksheets("sheet1") For Each cell In Range("N7:n900") If UCase(cell.Value) = "DONE" Then cell.EntireRow.Copy Worksheets("sheet2").Range("a" & i).PasteSpecial cell.EntireRow.Delete i = i + 1 End If Next End With Application.ScreenUpdating = True End Sub -- Gary "Steven" wrote in message oups.com... Hi, all. I faced VBA issue. IF you can spare me some time to do this project, it would be super nice. On sheet1, I will select entire row by clicking row number on the left, and I wisg I have a macro to send this selected row to next available row in sheet2. I used something like this, but doesn't help. Private Sub to1() Selection.Cut Sheets("1").Range("b65000").End(xlUp).Offset(1, 0).Value = Sheets("Prioritization").Selection End Sub and I also used this. Sub CutData() Dim sStr As String, Cell As Range, rng As Range sStr = "#Done#" For Each Cell In Range("n7:n900") If InStr(1, sStr, Cell.Value, vbTextCompare) 0 Then If rng Is Nothing Then Set rng = Cell Else Set rng = Union(rng, Cell) End If End If Next If Not rng Is Nothing Then rng.EntireRow.Copy Destination:=Worksheets( _ "OtherSheet").Range("A1") rng.EntireRow.Delete End If End Sub But you know what? If I can have a macro actually sends rows contains "done" from sheet1 to sheet2. it would be super nice. Once the row has "done" in it, "done" will be located in "N" column. Thank you again. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cut rows if contains "Done" from sheet1, and paste onto next available row in sheet2 in the same workbook.
try something like this
data in 4 th row in sheet1 is copited to 7th row in sheet2 Public Sub test() Worksheets("sheet1").Rows("4:4").Copy Worksheets("sheet2").Rows("7:7").PasteSpecial application.CutCopyMode=false worksheets("sheet2").range("a7").select End Sub ================== "Steven" wrote in message oups.com... Hi, all. I faced VBA issue. IF you can spare me some time to do this project, it would be super nice. On sheet1, I will select entire row by clicking row number on the left, and I wisg I have a macro to send this selected row to next available row in sheet2. I used something like this, but doesn't help. Private Sub to1() Selection.Cut Sheets("1").Range("b65000").End(xlUp).Offset(1, 0).Value = Sheets("Prioritization").Selection End Sub and I also used this. Sub CutData() Dim sStr As String, Cell As Range, rng As Range sStr = "#Done#" For Each Cell In Range("n7:n900") If InStr(1, sStr, Cell.Value, vbTextCompare) 0 Then If rng Is Nothing Then Set rng = Cell Else Set rng = Union(rng, Cell) End If End If Next If Not rng Is Nothing Then rng.EntireRow.Copy Destination:=Worksheets( _ "OtherSheet").Range("A1") rng.EntireRow.Delete End If End Sub But you know what? If I can have a macro actually sends rows contains "done" from sheet1 to sheet2. it would be super nice. Once the row has "done" in it, "done" will be located in "N" column. Thank you again. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cut rows if contains "Done" from sheet1, and paste onto next available row in sheet2 in the same workbook.
forgot, since rows are being deleted, have to go backwards.
you can set variables if you like, but i explicitly defined everything here Sub move_rows() Dim i As Long, x As Long i = 4 x = 7 Application.ScreenUpdating = False For x = 900 To 7 Step -1 If UCase(Range("n" & x).Value) = "DONE" Then Range("n" & x).EntireRow.Copy Worksheets("sheet2").Range("a" & i).PasteSpecial Range("n" & x).EntireRow.Delete i = i + 1 End If Next Application.ScreenUpdating = True Application.CutCopyMode = False End Sub -- Gary "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... here's a very basic concept. it will copy any row that has done in column n from 7 to 900 then copy it to sheet2 starting in row 4 then delete the row on sheet1 adapt to your needs Sub move_rows() Dim i As Integer i = 4 Application.ScreenUpdating = False With Worksheets("sheet1") For Each cell In Range("N7:n900") If UCase(cell.Value) = "DONE" Then cell.EntireRow.Copy Worksheets("sheet2").Range("a" & i).PasteSpecial cell.EntireRow.Delete i = i + 1 End If Next End With Application.ScreenUpdating = True End Sub -- Gary "Steven" wrote in message oups.com... Hi, all. I faced VBA issue. IF you can spare me some time to do this project, it would be super nice. On sheet1, I will select entire row by clicking row number on the left, and I wisg I have a macro to send this selected row to next available row in sheet2. I used something like this, but doesn't help. Private Sub to1() Selection.Cut Sheets("1").Range("b65000").End(xlUp).Offset(1, 0).Value = Sheets("Prioritization").Selection End Sub and I also used this. Sub CutData() Dim sStr As String, Cell As Range, rng As Range sStr = "#Done#" For Each Cell In Range("n7:n900") If InStr(1, sStr, Cell.Value, vbTextCompare) 0 Then If rng Is Nothing Then Set rng = Cell Else Set rng = Union(rng, Cell) End If End If Next If Not rng Is Nothing Then rng.EntireRow.Copy Destination:=Worksheets( _ "OtherSheet").Range("A1") rng.EntireRow.Delete End If End Sub But you know what? If I can have a macro actually sends rows contains "done" from sheet1 to sheet2. it would be super nice. Once the row has "done" in it, "done" will be located in "N" column. Thank you again. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy data from sheet2 to sheet1 when sheet2 has variable # of rows | Excel Discussion (Misc queries) | |||
how do i convert this to a formula +"sheet1!J"&(sheet2!A30)-1 | Excel Worksheet Functions | |||
how do copy "sheet1!A1+1 in sheet2 to sheet 3 and get "sheet2!A1+ | Excel Discussion (Misc queries) | |||
Complex if test program possible? If "value" "value", paste "value" in another cell? | Excel Discussion (Misc queries) | |||
use variable in Workbooks("book1").Worksheets("sheet1").Range("a1" | Excel Programming |