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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please check the attached.
CDI-Escalation-NO-ResponseTracking-.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.