+ Reply to Thread
Results 1 to 9 of 9

Move entire row from one sheet to another based on cell value & delete the original row

Hybrid View

  1. #1
    Registered User
    Join Date
    09-05-2013
    Location
    North Carolina, USA
    MS-Off Ver
    Excel 2007
    Posts
    4

    Move entire row from one sheet to another based on cell value & delete the original row

    Move entire row from one sheet to another based on cell value.
    I am trying to setup my spreadsheet so that when a row’s status in one sheet (Dashboard) is changed to “Closed,” the entire row is copied, deleted and then placed in another sheet (Completed).

    Everything is working except for the “deleting” the original cell in the Dashboard sheet.

    I hope I explained in enough detail. Please look at my code and see what I am doing wrong! I also attached my excel spreadsheet.

    Sub cuttosheet()
    Dim sRng As Range, cell As Range
    Dim dRng As Range
    Set sRng = Sheets("Dashboard").Range([A1], [A65536].End(xlUp))
    For Each cell In sRng
    If cell.Value = "Closed" Then
    Set dRng = Sheets("Completed").[A65536].End(xlUp)(2, 1)
    cell.EntireRow.Cut dRng
    End If
    Next
    End Sub
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A2:A65536")) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    If Target = "" Then Exit Sub
    Dim NR As Long
    With Application
      .EnableEvents = False
      .ScreenUpdating = False
      Select Case Target.Value
        Case "closed"
          Range("A" & Target.Row & ":Q" & Target.Row).Copy Worksheets("Completed").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
          Target.EntireRow.Delete Shift:=xlUp
       End Select
      .EnableEvents = True
      .ScreenUpdating = True
    End With
    End Sub
    
    Sub MoveMe(rTarget As Range)
        Dim wsMoveTo    As Worksheet
        Dim rMoveToRow  As Range
        Dim rCopyFrom   As Range
        Dim rCopyTo     As Range
        Dim rItem       As Range
        Dim rMoveUp     As Range
        Dim bEvents     As Boolean
        Dim bScrUpd     As Boolean
        Dim lCalc       As Long
        
        With Application
            bEvents = .EnableEvents
            bScrUpd = False
            lCalc = .Calculation
            .EnableEvents = False
            .ScreenUpdating = False
            .Calculation = xlCalculationManual
        End With
    
        Select Case rTarget.Text
            Case "closed"
                Set wsMoveTo = Sheets(rCompleted)
            Case Else
                Exit Sub
        End Select
        Set rMoveToRow = wsMoveTo.Cells.Find("Potential", LookIn:=xlValues, LookAt:=xlWhole).End(xlUp).Offset(1)
        Set rCopyFrom = Range(Cells(rTarget.Row, "A"), Cells(rTarget.Row, "Q"))
        Set rCopyTo = wsMoveTo.Range(wsMoveTo.Cells(rMoveToRow.Row, "A"), wsMoveTo.Cells(rMoveToRow.Row, "Q"))
        rCopyTo.Formula = rCopyFrom.Formula
        Set rMoveUp = Range(Cells.Find("Potential", LookIn:=xlValues, _
            LookAt:=xlWhole).End(xlUp).Offset(1), rCopyFrom)
        rMoveUp.Formula = rMoveUp.Offset(1).Formula
    
        
        Set wsMoveTo = Nothing
        Set rMoveToRow = Nothing
        Set rCopyFrom = Nothing
        Set rCopyTo = Nothing
        
        With Application
            .EnableEvents = bEvents
            .ScreenUpdating = bScrUpd
            .Calculation = lCalc
        End With
    End Sub
    Thanks!!!
    Attached Files Attached Files
    Last edited by ceciliacrawford; 09-09-2013 at 03:02 PM.

  2. #2
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,167

    Re: Move entire row from one sheet to another based on cell value & delete the original ro

    Try this code
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Columns("A:A")) Is Nothing Then
        If Range("A" & Target.Row).Value = "Closed" Then
            Target.EntireRow.Copy Worksheets("Completed").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
            Target.EntireRow.Delete
        End If
    End If
    End Sub
    Right click on the Dashboard sheet and select View Code. Copy this code over. Each time you type "Closed" in column A, it will move the row.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    Registered User
    Join Date
    09-05-2013
    Location
    North Carolina, USA
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Move entire row from one sheet to another based on cell value & delete the original ro

    Rows clears as before, but are not deleting and shifting up.
    Thanks

  4. #4
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,167

    Re: Move entire row from one sheet to another based on cell value & delete the original ro

    It does move up. I just tested it at my end and it works. Please try again and let me know.

  5. #5
    Registered User
    Join Date
    09-05-2013
    Location
    North Carolina, USA
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Move entire row from one sheet to another based on cell value & delete the original ro

    Tried again and mine still isn't moving up. Can you upload the file that works? So, I can see what I am doing wrong. I really do appreciate your help.

  6. #6
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,167

    Re: Move entire row from one sheet to another based on cell value & delete the original ro

    Here is my version. Just put in Closed (type it out) in column A and when you press enter, the row vanishes.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    09-05-2013
    Location
    North Carolina, USA
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Move entire row from one sheet to another based on cell value & delete the original ro

    IT'S WORKING...you are a genius!!! THANK YOU SOOOOO MUCH!!!!!
    Cecilia

  8. #8
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,167

    Re: Move entire row from one sheet to another based on cell value & delete the original ro

    Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

    In future, to mark your thread as Solved, you can do the following -
    Select Thread Tools-> Mark thread as Solved.

    Incase your issue is not solved, you can undo it as follows -
    Select Thread Tools-> Mark thread as Unsolved.

    Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.

  9. #9
    Registered User
    Join Date
    08-13-2013
    Location
    surat
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Move entire row from one sheet to another based on cell value & delete the original ro

    thank u so much arlu1201

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Move entire row from one sheet to another based on cell value.
    By romanm in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-29-2013, 06:20 PM
  2. Move entire row to another sheet based on cell value
    By Hoodalolly in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-23-2013, 05:18 PM
  3. Move entire row to a new sheet based on cell value vba help
    By almasdour in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-15-2013, 01:38 PM
  4. [SOLVED] Move entire row to another worksheet based on cell value
    By mmctague in forum Excel General
    Replies: 11
    Last Post: 06-26-2012, 12:43 PM
  5. move files to another folder delete original
    By D_Rennie in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-09-2009, 02:50 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1