Results 1 to 4 of 4

Thread: Move row to specified sheets based on multiple cell values in column F
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jan 2017
    Location
    South Africa
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Move row to specified sheets based on multiple cell values in column F

    Good day,

    I have code,

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    'Version 2
    If Not Intersect(Target, Range("F:F")) Is Nothing Then
    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    Dim Lastrow As Long
    Lastrow = Sheets("Completed").Cells(Rows.Count, "F").End(xlUp).Row + 1
    
    
    If Target.Value = "Yes" Then
    Rows(Target.Row).Copy Destination:=Sheets("Completed").Rows(Lastrow)
    Rows(Target.Row).Delete
    End If
    End If
    End Sub




    -----------------------------------------------
    With this code on the current sheet it checks the value of cell F in the row, if it is "Yes" it moves the entire row to sheet "Completed" and delete the row on the source sheet. I want to expand the code, when cell F in the row has "Parking Bay" it must move entire row to sheet "Parking Bay". Both the Yes and Parking Bay must work.

    Please assist to change

  2. #2
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,002
    Post Thanks / Like
    Mentioned
    60 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Move row to specified sheets based on multiple cell values in column F

    Try something like this.
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    'Version 2
    Dim wsDst As Worksheet
    Dim Lastrow As Long
    
        If Not Intersect(Target, Range("F:F")) Is Nothing Then
        
            If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
            
            Select Case Target.Value
                Case "Yes"
                    Set wsDst = Sheets("Completed")
                Case "Parking Bay"
                    Set wsDst = Sheets("Parking Bay")
            End Select
            
            If Not wsDst Is Nothing Then
            
                With wsDst
                    Lastrow = .Cells(Rows.Count, "F").End(xlUp).Row + 1
                    Rows(Target.Row).Copy Destination:=.Rows(Lastrow)
                End With
                
                Rows(Target.Row).Delete
            
            End If
        
        End If
    
    End Sub
    If posting code please use code tags.

  3. #3
    New Member
    Join Date
    Jan 2017
    Location
    South Africa
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Move row to specified sheets based on multiple cell values in column F

    The code works fine, I have changed to word "Parking Bay" to "Review", It does not work however when the sheet name "Parking Bay" and word has two words, "Parking Bay", but when changed to "Review" it works.

    Thanks for the help

  4. #4
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,002
    Post Thanks / Like
    Mentioned
    60 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Move row to specified sheets based on multiple cell values in column F

    It shouldn't matter how many words there are typed in the cell or in the sheet name, if you enter 'Parking Bay' in column F the row should be copied to the sheet named 'Parking Bay'.
    If posting code please use code tags.

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
  •