Drop-Down Option Moves Entire Row to Different Sheet

March 13, 2019 at 09:40:41
Specs: Windows 10
Is there a method to have a drop-down selection move an entire row to another sheet? I have multiple sheets to better organize item status. New, pending, and completed. Instead of simply copying a row and pasting it onto another sheet, could I have a drop down box that automatically moves the entire row?

Example: Drop down option "Pending" moves the entire row from sheet 1 to sheet 2 and lists the row on the bottom of sheet 2's list.

Is this a function that Excel can handle?

Thank you.


See More: Drop-Down Option Moves Entire Row to Different Sheet

Reply ↓  Report •

#1
March 13, 2019 at 09:46:27
I should have searched better before posting this. I was able to make this function work with this answer.

https://www.computing.net/answers/o...


Reply ↓  Report •

#2
March 13, 2019 at 12:47:58
I've used the above link to create the function I need. Is there a way I can have Excel populate a "Date" cell on the new sheet once the row was moved? In the "Completed" sheet, it would be nice if the document automatically logged when the row was moved to that sheet. In other words, when the row was completed.

Here is what I have so far:

If Target.Column = 9 Then
  If Target = "Completed" Then
    Application.EnableEvents = False
      nxtRow = Sheets("Completed").Range("I" & Rows.Count).End(xlUp).Row + 1
       Target.EntireRow.Copy _
        Destination:=Sheets("Completed").Range("A" & nxtRow)
       Target.EntireRow.Delete
  End If
 End If
 Application.EnableEvents = True
End Sub


Reply ↓  Report •

#3
March 13, 2019 at 15:07:42
Choose the cell where you want the date and use the Date function.

For Date and Time, use Now

e.g.

Range("A1") = Now

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

Related Solutions

#4
March 14, 2019 at 07:07:46
The data I am moving is contained in columns A through I. Sheet 1 is called "NewOrder" and Sheet 2 is called "Pending". I have column I triggering the move when listed as "Pending".

I want the date to populate on Sheet 2 in column J, on the row that I just moved, to time stamp the change.

I used the formula you just provided in your last response and it is creating a timestamp in Sheet 1.

Could you edit the below to reflect the proper code?

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Column = 9 Then
  If Target = "Pending" Then
    Application.EnableEvents = False
      nxtRow = Sheets("Pending").Range("I" & Rows.Count).End(xlUp).Row + 1
       Target.EntireRow.Copy _
        Destination:=Sheets("Pending").Range("A" & nxtRow)
       Target.EntireRow.Delete
  End If
 End If
 Application.EnableEvents = True
End Sub

Secondly, If I copy this code twice into the View Code area, it bugs out. I would like it to move as noted above from Sheet 1 to Sheet 2. However, I also have a Sheet 3 called "Completed" and Sheet 4 called "Cancelled". It would be nice to have Sheet 1 move to Sheet 3 or 4 as well depending on what word is used in column I.

What needs to change for this to work without issue?

Thank you.


Reply ↓  Report •

#5
March 14, 2019 at 12:45:10
Second answer first:

There can only be one Worksheet_Change macro per sheet. All actions that you want take based on a change to that sheet must be included in that single macro. Basically, you say to the macro "If the change meets this criteria, execute these instructions. If the change meets this other criteria, execute these other instructions", etc.

Sometimes each set of instructions will need to be it's own section of code while other times there is commonality with what needs to be done (such as in your case) and the different criteria can share some code.

In your case, you could hardcode the names of the 3 sheets into 3 different sets of "Paste" instructions (like you did for "Pending") or you could use a variable in the Paste snippet and set that variable based on which Sheet you want the paste to occur in. I chose the "variable" option.

Something like this should work. It's untested because I don't have time to set up a test workbook.

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Column = 9 Then
  If Target = "Pending" Then destSht  = "Pending"
  If Target = "Completed" Then destSht  = "Completed"
  If Target = "Cancelled" Then destSht  = "Cancelled"
    Application.EnableEvents = False
      nxtRow = Sheets(destSht).Range("I" & Rows.Count).End(xlUp).Row + 1
       Target.EntireRow.Copy _
        Destination:=Sheets(destSht.Range("A" & nxtRow)
       Target.EntireRow.Delete
  End If
 End If
 Application.EnableEvents = True
End Sub

Next, a suggestion:

I would move the Application.EnableEvents = False instruction down to just before you delete the Target row. That is the only change you are making to that sheet so there is no need to disable events until that change is about to be made. If anything goes wrong with the Copy/Paste operation, the code may crash and Events will remain disabled. It's "safer" to disable Events only for the actions that require them to be disabled, reducing the possibility that an error will cause problems. Another option is to include an Error Handling routine that enables Events before the code exits if an error occurs.

re: "I used the formula you just provided in your last response and it is creating a timestamp in Sheet 1."

What I provided is a VBA instruction, not a formula. There is a big difference.

In any case, my suggestion was just an example of the syntax. I was leaving it up to you to modify the "destination" based on your needs.

Based on the edit that I provided earlier in this post, the following suggestion should work. (again, untested).

Obviously this is instruction needs to be executed after the values for destSht and nxtRow have been determined.

Sheets(destSht).Range("J" & nxtRow) = Date

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#6
March 15, 2019 at 10:51:20
I've played around with the code you provided. For starters, I needed to add an end parenthesis character on the 9th line, as such:

Destination:=Sheets(destSht).Range("A" & nxtRow)

Simple enough. The debug highlights the second "End If" and gives me an error message of:

Compile error:
End If without block if

Please advise on the above.
______

To add further complication to this, is there a way I can set the date cell to be different in each sheet and not use "J" every time. Let me try to explain this further.

Sheet 1 "NewUser": Nothing in Column J.

Sheet 2 "Pending": Will input a date in Column J by use of the code you provided when moving from Sheet 1 to Sheet 2.

Sheet 3 "Completed": Will input a date in Column K by use of the code you provided, changing the J to a K, from Sheet 2 to Sheet 3.

I other words, on Sheets 3, there will be Column J showing "Date Submitted" and Column K showing "Date Completed". If the user account goes through each sheet, this should be no issue. Where this becomes complicated is when a user does NOT hit a pending status, and thus never shows on Sheet 2. There are times when an account is completed immediately depending on the type of account. In this circumstance, I would move from Sheet 1 to Sheet 3, but I can't have the "Date Completed" populate on Column J. How would I change the code to reflect this?


Reply ↓  Report •

#7
March 15, 2019 at 12:34:06
re: The missing parenthesis

Like I said, untested. ;-) I'm glad you were able to fix it.

re: The End If error

Like I said, untested. ;-)

I added 2 more If instructions. (Completed and Cancelled) You'll need to add an End If for each of those in with the other End If's. I missed that.

re: The Date Issue

I'm confused, mainly because you know your work process better then I do. I don't really need to know about "user accounts", etc. I just need to know where the date needs to go. Try to keep it simple and just discuss the Excel/VBA part of the issue.

Are you saying that you want to use Column J on the Pending sheet, Column K on the Completed sheet, Column L on the Cancelled sheet?

Or...

Are you saying that the destination for the Date depends on whether or not there is already a Date in a given cell on a given sheet?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#8
March 15, 2019 at 12:38:05
"Are you saying that you want to use Column J on the Pending sheet, Column K on the Completed sheet, Column L on the Cancelled sheet?"

This is correct.


Reply ↓  Report •

#9
March 15, 2019 at 12:47:04
re: End If error

Adding more End If resulted in the same issue. Interestingly, having a single End If instead of 2 (or 4) looks to be working correctly. Is this acceptable, or will this create another issue?


Reply ↓  Report •

#10
March 15, 2019 at 13:08:54
No, a single End If is correct. I answered too quickly because I'm rushing to get out of the office. Sorry.

The 3 If's for the sheet names are not Block If's so they don't need End If's. The only End If required is for the If Target.Column = 9 Then instruction.

That is a Block If because there is a list of instructions that follow. The If's for the sheet names are just simple If statements. If this Then that. Done.

I'll work on the Column issue later. Gotta run.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#11
March 19, 2019 at 07:18:37
I was able to get the date to work with this code. If there is a better way to go about this, please let me know. The issue I have now is that while the date places correctly, the row replaced row 2 in every sheet instead of adding new rows to a list.

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Column = 9 Then
  If Target = "Pending" Then destSht = "Pending"
  If Target = "Completed" Then destSht = "Completed"
  If Target = "Suspended" Then destSht = "Suspended"
    Application.EnableEvents = False
      nxtRow = Sheets(destSht).Range("L" & Rows.Count).End(xlUp).Row + 1
       Target.EntireRow.Copy _
        Destination:=Sheets(destSht).Range("A" & nxtRow)
       Target.EntireRow.Delete
    If destSht = "Pending" Then Sheets(destSht).Range("J" & nxtRow) = Date
    If destSht = "Completed" Then Sheets(destSht).Range("K" & nxtRow) = Date
    If destSht = "Suspended" Then Sheets(destSht).Range("L" & nxtRow) = Date
  End If
  Application.EnableEvents = True
End Sub


Reply ↓  Report •

#12
March 20, 2019 at 18:37:13
I think the following code is what you are looking for. Tell me if not.

First, some comments about your latest version of the code:

1 - You still haven't taken my advice about the placement of the Application.EnableEvents = False instruction. If you want to risk having Events remain disabled because of a code error, feel free. That would not be my choice and therefore I placed that instruction in a location that I feel is safer.

2 - re: "The issue I have now is that while the date places correctly, the row replaced row 2 in every sheet "

I can't see your sheet from where I'm sitting, so I can't only guess as to why that is happening. I think (guess) that it is because of this instruction:

nxtRow = Sheets(destSht).Range("L" & Rows.Count).End(xlUp).Row + 1

You are determining the next empty row based on the data in Column L. I suggest that you use Column A since it appears that Column A has data in every row, unlike Column L.

If I'm not mistaken, you have drop downs in every sheet and you are moving rows from sheet to sheet, correct? If that's the case, then you should put a copy of this code in every sheet. If you are only moving data from a "main sheet" to one of the other three, then you only need the code in the main sheet.

Private Sub Worksheet_Change(ByVal Target As Range)

'Determine If Column I (Drop Down) Was Changed
  If Target.Column = 9 Then

'Determine Destination Sheet and Date Column
   If Target = "Pending" Then
      destSht = "Pending"
      dateCol = "J"
   End If
   
   If Target = "Completed" Then
      destSht = "Completed"
      dateCol = "K"
   End If
   
   If Target = "Suspended" Then
      destSht = "Suspended"
      dateCol = "L"
   End If

'Determine Next Available Row On Destination Sheet
      nxtRow = Sheets(destSht).Range("A" & Rows.Count).End(xlUp).Row + 1
      
'Copy Row Based On Drop Down Choice
       Target.EntireRow.Copy _
         Destination:=Sheets(destSht).Range("A" & nxtRow)
         Sheets(destSht).Range(dateCol & nxtRow) = Date

'Delete Row In Which Choice Was Made
    Application.EnableEvents = False
      Target.EntireRow.Delete
    Application.EnableEvents = True
  End If
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#13
March 21, 2019 at 10:26:10
That code works flawlessly to move rows as I need between sheets. The only addition I need is for the "Completed" sheet to sort the rows based off of the date in Column A.

Reply ↓  Report •

#14
March 22, 2019 at 15:14:05
I'm travelling for a few days. I'll see what I can do as time allows.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#15
March 25, 2019 at 06:13:16
No problem. Thank you for your continued assistance.

Reply ↓  Report •

Ask Question