VBA to Cut and Paste entire Row to New sheet based on Cell Value
Hello Everyone,
I have a dashboard with lots of details, in that i have to cut and paste the completed row's to new sheet.(Manually)
Could you please anyone help me out to complete the below steps in VBA.
If i enter/select "Completed" in column "AA1" then one pop up will be shown, if i click yes on that, the entire Row should move to next sheet last row. else i click NO then the program should stop.
If it is done by VBA,, it will very helpful and it will save lots of time.
Thanks in Advance
Re: VBA to Cut and Paste entire Row to New sheet based on Cell Value
What code have you got so far?
Re: VBA to Cut and Paste entire Row to New sheet based on Cell Value
Hello Ecniv,
Quote:
. What code have you got so far
Actually I don't Any code for this.
Re: VBA to Cut and Paste entire Row to New sheet based on Cell Value
Hello Ecniv,
Quote:
. What code have you got so far
Actually I don't Any code for this.
Re: VBA to Cut and Paste entire Row to New sheet based on Cell Value
For a start then, try recording a macro of your actions and see if you understand the code it generates. Only as a starter is in reality you'd need to have objects and things to clarify.
How much do you know about vba coding?
I think there is a thread near the top (pinned) which has numerous examples about things in vba/excel. Probably the answer is in there.
When you have some code (that works or not) post back to let us know how you are getting on with it
Re: VBA to Cut and Paste entire Row to New sheet based on Cell Value
Quote:
If i enter/select "Completed" in column "AA1"
should we assume that you mean column AA and the specific row number you are working with?
it was really easy for me to write the code for what you want, but (as pointed out by ecniv) the forum is not a free code writing service, it is to help people to learn to write their own stuff or improve /fix what they have already done
Re: VBA to Cut and Paste entire Row to New sheet based on Cell Value
Hi Westconn,
Thanks for your Reply,
I have a below code now but there is some error.
Code:
Sub Test()
Dim sht1 As Worksheet, sht2 As Worksheet
Dim i As Long
Dim Msg As String, Ans As Variant
Msg = "Are you sure, Did you Completed this Project?" & vbNewLine & "Would you like to move the data to Sheet 2?"
Ans = MsgBox(Msg, vbYesNo, "Please Confirm")
Set sht1 = ThisWorkbook.Worksheets("Sheet1")
Set sht2 = ThisWorkbook.Worksheets("Sheet2")
Select Case Ans
Case vbYes
For i = 2 To sht1.Cells(sht1.Rows.Count, "AA").End(xlUp).Row
If sht1.Range("AA" & i).Value = "Completed" Then
sht1.Range("A" & i).EntireRow.Cut sht2.Range("A" & sht2.Cells(sht2.Rows.Count, "AA").End(xlUp).Row + 1)
End If
Next i
Case vbNo
GoTo Quit:
End Select
Quit:
End Sub
This code is working perfectly, but it will not remove that row from Sheet1, it just copy the data from sheet1 to sheet2.
and please tell me how to add this code into workseet_change procedure, because when i select/enter as completed immediately this will run.
Thanks
Re: VBA to Cut and Paste entire Row to New sheet based on Cell Value
Code:
For i = 2 To sht1.Cells(sht1.Rows.Count, "AA").End(xlUp).Row
If sht1.Range("AA" & i).Value = "Completed" Then
sht1.Range("A" & i).EntireRow.Cut sht2.Range("A" & sht2.Cells(sht2.Rows.Count, "AA").End(xlUp).Row + 1)
sht1.Range("A" & i).EntireRow.Delete
End If
Next i
Make sure you have a back up of the xls before you run as once it runs I dont think you can get the rows back
Try the above.. see if it errors or does it wrong
Edit:
Or perhaps
Code:
sht1.Rows(i).Delete
instead of the bold line I put in above.
Stackoverflow has some ( a few) threads on deleting, this is the one I read, also has some ideas about perhaps removing just one cells data and resorting the data so as to confirm all is ok...
Re: VBA to Cut and Paste entire Row to New sheet based on Cell Value
here is my take on what you wanted
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 27 And Target = "Completed" Then
If MsgBox("move entire row to next sheet?", vbYesNo) = vbYes Then
Application.EnableEvents = False
Target.EntireRow.Copy Sheets(Target.Parent.Index + 1).Cells(Rows.Count, 1).End(xlUp).Offset(1)
Target.EntireRow.Delete
Application.EnableEvents = True
End If
End If
End Sub
Re: VBA to Cut and Paste entire Row to New sheet based on Cell Value
Hi Westconn,
Exactly, your code working awesome.
Thank you so much.
Hello Ecniv,
I have tried your code, but it's not remove the entire row, again it just copy the data from that row to next sheet?
I have tried both the options you mentioned but it's not working.
Quote:
Code:
For i = 2 To sht1.Cells(sht1.Rows.Count, "AA").End(xlUp).Row
If sht1.Range("AA" & i).Value = "Completed" Then
sht1.Range("A" & i).EntireRow.Cut sht2.Range("A" & sht2.Cells(sht2.Rows.Count, "AA").End(xlUp).Row + 1)
sht1.Range("A" & i).EntireRow.Delete
End If
Next i
Make sure you have a back up of the xls before you run as once it runs I dont think you can get the rows back
Try the above.. see if it errors or does it wrong
Edit:
Or perhaps
Code:
sht1.Rows(i).Delete
Re: VBA to Cut and Paste entire Row to New sheet based on Cell Value
Hi Westconn,
Exactly, your code working awesome.
Thank you so much.
Hello Ecniv,
I have tried your code, but it's not remove the entire row, again it just copy the data from that row to next sheet?
I have tried both the options you mentioned but it's not working.
Quote:
Code:
For i = 2 To sht1.Cells(sht1.Rows.Count, "AA").End(xlUp).Row
If sht1.Range("AA" & i).Value = "Completed" Then
sht1.Range("A" & i).EntireRow.Cut sht2.Range("A" & sht2.Cells(sht2.Rows.Count, "AA").End(xlUp).Row + 1)
sht1.Range("A" & i).EntireRow.Delete
End If
Next i
Make sure you have a back up of the xls before you run as once it runs I dont think you can get the rows back
Try the above.. see if it errors or does it wrong
Edit:
Or perhaps
Code:
sht1.Rows(i).Delete