none
Move row into another sheet based on dropdown value RRS feed

  • Question

  • Greetings,

    I am very new to the concept of VBA and have been searching for the right code that will work for my workbook. My workbook contains two worksheets titled "Active HIL" and "Removed from HIL." If I select "None" from the dropdown list in Column 9 on the Active HIL sheet, I want a warning message box to pop before I continue, then move the entire row to the "Removed from HIL" sheet. In my code below, I chose clearcontents vs delete as I want to re-use the row from the Active HIL sheet. Here are my problems:

    1. Can't get the message box to pop up/work right (I ended up deleting this from my code below)

    2. Once row has been moved, it pastes over the last row copied, vs pasting into the next available row down.

    This is the code I am currently using but can't get right as I have simply copied/pasted from various excel sites. Any help at all would be MUCH appreciated!! Please and thank you!

    Private Sub Worksheet_Change(ByVal Target As Range)

        Dim AHIL As Worksheet

        Dim RHIL As Worksheet

        Dim strdc As String

        Application.ScreenUpdating = False

        Application.Calculation = xlCalculationManual

        If Target.Cells.Count > 1 Then Exit Sub

        

        If Target.Column <> 9 Then Exit Sub

        

        Set AHIL = ThisWorkbook.Sheets("Active HIL")

        Set RHIL = ThisWorkbook.Sheets("Removed from HIL")

             

        With AHIL

            strdc = Target.Value

            

            If strdc = "None" Then

                n = .Rows.Count

                Target.EntireRow.Copy

                RHIL.Range("A" & n).End(xlUp).Offset(1, 0).PasteSpecial xlValues

                RHIL.Range("A" & n).End(xlUp).EntireRow.PasteSpecial xlPasteFormats

                Application.CutCopyMode = False

                Target.EntireRow.ClearContents

            End If

            

        End With

        

        Application.ScreenUpdating = True

        Application.Calculation = xlCalculationAutomatic

    End Sub

    Monday, January 18, 2016 11:13 PM

Answers

  • Hi

    I tried your macro and it works okay, Re: Your second comment.

     You did not say what kind of Message box so try this:

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim AHIL   As Worksheet
        Dim RHIL   As Worksheet
        Dim strdc  As String, n
        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual
       
        If Target.Cells.Count > 1 Then Exit Sub
        If Target.Column <> 9 Then Exit Sub
       
        Set AHIL = ThisWorkbook.Sheets("Active HIL")
        Set RHIL = ThisWorkbook.Sheets("Removed from HIL")
       
        With AHIL
            strdc = Target.Value
            If strdc = "None" Then
            MsgBox "You are about to transfer over to Remove from HIL"
                n = .Rows.Count
                Target.EntireRow.Copy
                RHIL.Range("A" & n).End(xlUp).Offset(1, 0).PasteSpecial xlValues
                RHIL.Range("A" & n).End(xlUp).EntireRow.PasteSpecial xlPasteFormats
                Application.CutCopyMode = False
                Target.EntireRow.ClearContents
            End If
        End With

        Application.ScreenUpdating = True
        Application.Calculation = xlCalculationAutomatic

    End Sub


    Cimjet

    • Proposed as answer by André Santo Tuesday, January 19, 2016 5:43 PM
    • Marked as answer by David_JunFeng Friday, January 29, 2016 9:46 AM
    Tuesday, January 19, 2016 1:07 AM