VBA code not pasting data correctly, keeps overwriting data in target sheet

[handoverhammer]

Hi Excel Wizards,

The function of this code is to copy data from one sheet to another using a drop down and message box, also adding a timestamp. Unfortunately the destination is targeting row 2, not the last blank row, and overwriting any data in row 2 of the destination sheet when the copy is triggered in the drop down.

FYI: drop down is in "H", the rows being copied are A:I, time stamp is in "F"

Here's what I have right now:

Private Sub Worksheet_Change(ByVal Target As Range)If Not Intersect(Target, Range("H:H")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim Lastrow As Long
Dim ans As String
ans = Target.Value
Dim Response As VbMsgBoxResult
If ans = "Initial" Then
Response = MsgBox("Move to Sandbox?", vbQuestion + vbYesNo)
End If
If Response = vbNo Then Exit Sub
If ans = "Initial" Then
Lastrow = Sheets("Sandbox").Cells(Rows.Count, "I").End(xlUp).Row + 1
Cells(Target.Row, "F").Value = Now
Rows((Target.Row)).Copy Destination:=Sheets("Sandbox").Rows(Lastrow): Rows(Target.Row).Delete
Sheets("Sandbox").Select
End If

'

Please advise.

Thank you!

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("H:H")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
    Application.ScreenUpdating = False
    If Target = "Initial" Then
        If MsgBox("Move to Sandbox?", vbQuestion + vbYesNo) = vbNo Then
            Exit Sub
        Else
            Cells(Target.Row, "F").Value = Now
            Target.EntireRow.Copy Sheets("Sandbox").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        End If
    End If
    Target.EntireRow.Delete
    Sheets("Sandbox").Select
    Application.ScreenUpdating = True
End Sub

 

[handoverhammer]

That sort of works. But there are also six other sheets that are triggered by 6 other drop down options. I've removed the Application ScreenUpdating because I'm not sure where to put it with multiple triggers.

For our purposes, Here are two, no difference between two or six I don't think. The problem is, I'm getting object required errors now. Am I missing something about dividing these when there are more than one?

Private Sub Worksheet_Change(ByVal Target As Range)If Intersect(Target, Range("H:H")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub


If Target = "Sheet2" Then
Response = MsgBox("Move to Sheet2?", vbQuestion + vbYesNo)
If Response = vbNo Then
Exit Sub
Else
Cells(Target.Row, "F").Value = Now
Target.EntireRow.Copy Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
End If
End If
Target.EntireRow.Delete
Sheets("Sheet2").Select


If Target = "Sheet3" Then
Response = MsgBox("Move to Sheet3?", vbQuestion + vbYesNo)
If Response = vbNo Then
Exit Sub
Else
Cells(Target.Row, "F").Value = Now
Target.EntireRow.Copy Sheets("Sheet3").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
End If
End If
Target.EntireRow.Delete
Sheets("Sheet3").Select

End Sub

Thoughts?

 

[mumps]

In the latest code you posted, you no longer have the criteria that the target cell in column H must be equal to "Initial". Is this the case? Also, you don't use the sheet named "Sandbox". Please clarify. How many sheets in total have the drop down in column H? What are the names of those sheets? Are there any other sheets in your workbook besides the ones with the drop downs? If so, what are the names of those sheets?

According to your latest code, here is my understanding of what you want to do:
-Regardless of which sheet you are on, if you select "Initial" from the drop down in column H, if the user responds with a "Yes", you want to add the date to column F and copy the entire row to the bottom of that particular sheet. Is this correct?

 

[handoverhammer]

Hi Mumps,

Sorry for the confusion. I simplified the criteria. For our purposes, the drop down list will include "Sheet2" "Sheet3" etc, and upon selection will copy the data to the corresponding Sheet (2,3, etc). In total, there are six sheets with the drop down in Column H. We will be transferring data between Sheets 1-6.

Regardless of what sheet I am on, the drop down selection of "Sheet2" "Sheet3" etc will:
- trigger MsgBox
- trigger all below steps when "yes" is selected in MsgBox
- copy the data from A:H
- target the next (last) blank row of the corresponding sheet selected in drop down
- transfer the data to said corresponding sheet
- update Column F with "Now"
- delete data from row where drop down originated
- "select" sheet where all the data was sent for view/review

The use case for this is related to project management. Let's say you have a project with 6 stages, each stage has it's own sheet. The goal of this worksheet is to transfer the data of each job (row) in the project through each of the six stages.

 

[mumps]

Are there any other sheets in the workbook besides the 6 sheets with the drop downs? If so, what are their names?

 

[handoverhammer]

There are not any other sheets, only sheet1,2,3,4,5,6.

 

[mumps]

Place this macro in the code module for ThisWorkbook. Do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the left hand pane, double click on "ThisWorkbook". Copy/paste the macro into the empty window that opens up. Close the window to return to your sheet. Make a selection in column H of any sheet.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Intersect(Target, Range("H:H")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    If MsgBox("Move row " & Target.Row & " to " & Target & "?", vbQuestion + vbYesNo) = vbNo Then
        Exit Sub
    Else
        Cells(Target.Row, "F").Value = Now
        Target.EntireRow.Copy Sheets(Target.Value).Cells(Sheets(Target.Value).Rows.Count, "A").End(xlUp).Offset(1, 0)
    End If
    Sheets(Target.Value).Select
    Target.EntireRow.Delete
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

 

[handoverhammer]

Thank you!

One last thing, in column G there are "due dates" for the project. When the macro runs, it's final step should be to sort the target sheet's rows by these dates, in ascending order after "today" (upcoming dates + today). Conditional Formatting will not work for this.

Thoughts?

 

[mumps]

I'm not sure what you mean by

in ascending order after "today" (upcoming dates + today)

Could you please clarify using a few examples?