Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 22

Thread: Moving row based on value

  1. #1
    New Member
    Join Date
    Jun 2018
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Moving row based on value

    I am using the code below. When I activate the macro, the appropriate row disappears, but it is not pasted anywhere.


    Code:
    Sub EdgewoodOpen()
        Dim xRg As Range
        Dim xCell As Range
        Dim I As Long
        Dim J As Long
        Dim K As Long
        I = Worksheets("Edgewood-Open").UsedRange.Rows.Count
        J = Worksheets("Edgewood-Closed").UsedRange.Rows.Count
        If J = 1 Then
           If Application.WorksheetFunction.CountA(Worksheets("Edgewood-Closed").UsedRange) = 0 Then J = 0
        End If
        Set xRg = Worksheets("Edgewood-Open").Range("E1:E" & I)
        On Error Resume Next
        Application.ScreenUpdating = False
        For K = 1 To xRg.Count
            If CStr(xRg(K).Value) = "Complete" Then
                xRg(K).EntireRow.Copy Destination:=Worksheets("Edgewood-Closed").Range("A" & J + 1)
                xRg(K).EntireRow.Delete
                If CStr(xRg(K).Value) = "Complete" Then
                    K = K - 1
                End If
                J = J + 1
            End If
        Next
        Application.ScreenUpdating = True
    End Sub
    Last edited by Fluff; Jun 26th, 2018 at 01:37 PM. Reason: Code tags

  2. #2
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    6,108
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Moving row based on value

    Could you explain in words what you are trying to do using a few examples from your data referring to specific cells, rows, columns and worksheets.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    New Member
    Join Date
    Jun 2018
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Moving row based on value

    I am trying to copy a row from one spreadsheet (Edgewood-Open) to another (Edgewood-Closed) when the cell in column E states "Complete". Data is disappearing from the "Edgewood-Open" sheet but not transferring to "Edgewood-Closed". Please let me know if you need more info.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    11,266
    Post Thanks / Like
    Mentioned
    216 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Moving row based on value

    What happens if you remove this line
    Code:
    On Error Resume Next
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  5. #5
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    6,108
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Moving row based on value

    Try:
    Code:
    Sub CopyRows()
        Application.ScreenUpdating = False
        Dim LastRow As Long
        LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Sheets("Edgewood-Open").Range("E1:E" & LastRow).AutoFilter Field:=1, Criteria1:="Complete"
        Sheets("Edgewood-Open").Range("E2:E" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Edgewood-Closed").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        Sheets("Edgewood-Open").Range("E2:E" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        If Sheets("Edgewood-Open").AutoFilterMode = True Then Sheets("Edgewood-Open").AutoFilterMode = False
        Application.ScreenUpdating = True
    End Sub
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  6. #6
    New Member
    Join Date
    Jun 2018
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Moving row based on value

    same thing

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    11,266
    Post Thanks / Like
    Mentioned
    216 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Moving row based on value

    Do you get any error messages?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  8. #8
    New Member
    Join Date
    Jun 2018
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Moving row based on value

    no change. It was working earlier then my computer randomly restarted. When it started back up it didn't work.

  9. #9
    New Member
    Join Date
    Jun 2018
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Moving row based on value

    no error message

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    11,266
    Post Thanks / Like
    Mentioned
    216 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Moving row based on value

    Does mumps code work for you?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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