dcsimg
Results 1 to 11 of 11

Thread: VBA to Cut and Paste entire Row to New sheet based on Cell Value

  1. #1

    Thread Starter
    Member
    Join Date
    Sep 2017
    Posts
    37

    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

  2. #2
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Java (JSP) Hell!
    Posts
    5,278

    Re: VBA to Cut and Paste entire Row to New sheet based on Cell Value

    What code have you got so far?

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

  3. #3

    Thread Starter
    Member
    Join Date
    Sep 2017
    Posts
    37

    Re: VBA to Cut and Paste entire Row to New sheet based on Cell Value

    Hello Ecniv,
    . What code have you got so far
    Actually I don't Any code for this.

  4. #4

    Thread Starter
    Member
    Join Date
    Sep 2017
    Posts
    37

    Re: VBA to Cut and Paste entire Row to New sheet based on Cell Value

    Hello Ecniv,
    . What code have you got so far
    Actually I don't Any code for this.

  5. #5
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Java (JSP) Hell!
    Posts
    5,278

    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

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,651

    Re: VBA to Cut and Paste entire Row to New sheet based on Cell Value

    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
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  7. #7

    Thread Starter
    Member
    Join Date
    Sep 2017
    Posts
    37

    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

  8. #8
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Java (JSP) Hell!
    Posts
    5,278

    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...
    Last edited by Ecniv; Aug 7th, 2018 at 03:20 PM.

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

  9. #9
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,651

    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
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  10. #10

    Thread Starter
    Member
    Join Date
    Sep 2017
    Posts
    37

    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.
    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

  11. #11

    Thread Starter
    Member
    Join Date
    Sep 2017
    Posts
    37

    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.
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width