Move entire row to another workbook based on cell value

i am trying to create a VBA to move a row from sheet1 to sheet2 based on whether there is data in Column k or not but am having difficulty as i am new to excel and still learning.
CDI-Escalation-NO-ResponseTracking-.xlsx
Amy kanneAsked:
Who is Participating?
 
Subodh Tiwari (Neeraj)Connect With a Mentor Excel & VBA ExpertCommented:
Please check the attached.
CDI-Escalation-NO-ResponseTracking-.xlsm
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Hi Amy,

The detail you provided is not sufficient to derive a solution. Please elaborate it a bit more and upload a sample workbook (without any sensitive data) along with the desired output mocked up manually on another sheet, you may name that sheet as Desired.
0
 
Amy kanneAuthor Commented:
i have attached the workbook
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Sorry! At least I am not sure what are you trying to achieve here unless you clearly describe it.
0
 
Amy kanneAuthor Commented:
i want to be able to move  an entire row from sheet 1 to sheet 2 once a response is selected from the drop down in column K.  So, if column k is blank then the row stays on sheet 1 and if column k is filled in the row moves to sheet 2.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
The column K is not the last column on the Sheet1 but it is column L.
If you want the entire row to be moved onto the Sheet2 once you select an option in column K, how would you fill the column L then?
Or would you select an option in column K after filling the column L?

The above query makes sense if you need a Change Event Code for Sheet1 so that once you select the value in column K, the entire row would be moved to the Sheet2.

But if you want a button to manually run the code by hitting the button to move all the rows where column K has value in it, that will not be an issue.

What would you prefer then?

Also, on Sheet2, there are already some tables so where exactly you want the rows from Sheet1 to go?
0
 
Amy kanneAuthor Commented:
column L will be filled in periodically during the process so once column K is filled in it needs to go to sheet 2 regardless of whether there is data in column L or not.  on sheet 2 i had moved the headers from sheet 1 into sheet 2 so i would want the rows from sheet 1 to go underneath those headers
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may try something like this...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim dws As Worksheet
Dim dlr As Long, r As Long
Set dws = Sheets("Sheet2")
On Error GoTo Skip
If Target.Column = 11 And Target.Row > 3 Then
    If Target <> "" Then
        Application.EnableEvents = False
        r = Target.Row
        If dws.Range("A9").Value = "" Then
            dlr = 9
        Else
            dlr = dws.Cells(Rows.Count, 1).End(xlUp).Row + 1
        End If
        Range("A" & r & ":L" & r).Copy
        dws.Range("A" & dlr).PasteSpecial xlPasteValues
        dws.Range("A" & dlr).PasteSpecial xlPasteFormats
        Rows(r).Delete
    End If
End If
Skip:
Application.EnableEvents = True
End Sub

Open in new window

CDI-Escalation-NO-ResponseTracking-.xlsm
0
 
Amy kanneAuthor Commented:
this is moving the row but when i enter new data that i would like to work in the same fashion but it replaces what is on sheet 2 instead of adding another row to sheet 2.  i have attached an updated file
CDI-Escalation-NO-ResponseTracking-.xlsm
0
 
Amy kanneAuthor Commented:
this works perfectly. thank you so much for all of your help
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Amy! Glad it worked as desired.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.