Maximilian
09-14-2013, 07:24 AM
Hello!
Ive been trying to figure out how to make this work for a while now, so I'll give you guys a try!:)
I have a list of facts in Sheet1 from column A-Q and row 1-5000.
In column E im going to write different words as I go down the list. Lets just say that the word will be "Yes" or "No".
I want a macro that will copy all the rows with the word "Yes" in, into Sheet2.
I have found a way that works, but the problem is that this will copy into row 2 in sheet 2 always. So, lets say i change some of the info on Sheet2 and after a week i use the macro again. This will make the macro overwrite the changes ive done.
Not sure if you guys understand what im trying to say here, but I hope so!.
I would like a macro that MOVE the row, instead of Copy/paste it. This will make the number of rows on Sheet1 grow smaller, and I can change the info as i go down the list on sheet2.
I hope you get what im asking.
Below is the macro im using now:
Sub Makro1()
Dim LSearchRow As Integer
Dim LCopyToRow As Integer
On Error GoTo Err_Execute
LSearchRow = 2
LCopyToRow = 2
While Len(Range("A" & CStr(LSearchRow)).Value) > 0
If Range("E" & CStr(LSearchRow)).Value = "YES" Then
Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
Selection.Copy
Sheets("Sheet2").Select
Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
ActiveSheet.Paste
LCopyToRow = LCopyToRow + 1
Sheets("Sheet1").Select
End If
LSearchRow = LSearchRow + 1
Wend
Application.CutCopyMode = False
Sheets("Sheet1").Select
Exit Sub
Err_Execute:
MsgBox "An error occurred. Number: " & Err.Number & " Description: " & Err.Description
End Sub
Ive been trying to figure out how to make this work for a while now, so I'll give you guys a try!:)
I have a list of facts in Sheet1 from column A-Q and row 1-5000.
In column E im going to write different words as I go down the list. Lets just say that the word will be "Yes" or "No".
I want a macro that will copy all the rows with the word "Yes" in, into Sheet2.
I have found a way that works, but the problem is that this will copy into row 2 in sheet 2 always. So, lets say i change some of the info on Sheet2 and after a week i use the macro again. This will make the macro overwrite the changes ive done.
Not sure if you guys understand what im trying to say here, but I hope so!.
I would like a macro that MOVE the row, instead of Copy/paste it. This will make the number of rows on Sheet1 grow smaller, and I can change the info as i go down the list on sheet2.
I hope you get what im asking.
Below is the macro im using now:
Sub Makro1()
Dim LSearchRow As Integer
Dim LCopyToRow As Integer
On Error GoTo Err_Execute
LSearchRow = 2
LCopyToRow = 2
While Len(Range("A" & CStr(LSearchRow)).Value) > 0
If Range("E" & CStr(LSearchRow)).Value = "YES" Then
Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
Selection.Copy
Sheets("Sheet2").Select
Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
ActiveSheet.Paste
LCopyToRow = LCopyToRow + 1
Sheets("Sheet1").Select
End If
LSearchRow = LSearchRow + 1
Wend
Application.CutCopyMode = False
Sheets("Sheet1").Select
Exit Sub
Err_Execute:
MsgBox "An error occurred. Number: " & Err.Number & " Description: " & Err.Description
End Sub