Solved Move data to another sheet when deleted from original

August 22, 2017 at 06:06:11
Specs: Windows 10
Hi. Is there a way that I move data (row) to another sheet when deleted from original? I believe that it wil need to be coded in VB but ive got no idea what I'm doing. Thank you

See More: Move data to another sheet when deleted from original

Reply ↓  Report •

August 22, 2017 at 06:44:12
A couple of options come to mind:

1 - You could dedicate a column to accept a string that would automatically trigger a "move and delete" macro. Anything from entering an e.g. "X" in the column to choosing a specific item from a drop-down menu. This would be done with a Worksheet_Change macro which would monitor that column and react to the change if it met the criteria.

2 - You could add an item to the cell context menu (the menu you see when you right click a cell) to run the "move and delete" macro. Basically you would right click any cell in the target row and choose the macro name. This would require 2 macros: A (temporary) macro to first add the macro name to the context menu and then the macro that actually performs the "move and delete".

I have a number of actions that I perform on multiple sheets, many times a day. For example, I have a few standard formulas that I use in hundreds of worksheets. Instead of constantly typing those formulas, I wrote a macro to put them in the cell. When I want to enter one of those formulas, I right-click the cell, choose the macro name from the context menu and I'm done. No typing, no errors.

Do either of those options interest you?

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

message edited by DerbyDad03

Reply ↓  Report •

August 22, 2017 at 07:31:31
The 1st solution sounds good, but can an action say deleting a row be classed as a trigger criteria?

Reply ↓  Report •

August 22, 2017 at 08:43:40
✔ Best Answer
re: "...can an action say deleting a row be classed as a trigger criteria?"

Yes, the deletion of the row is definitely a trigger - because it is a change - but that is not what we would use as the trigger to copy/delete the row. If we did that, we would no longer have a row to Copy. In other words, if we use option 1, the "delete" wouldn't cause the copy/delete action, the entering of a specific string in a specific column would cause the copy/delete action. Allow me to explain...

The Worksheet_Change code will trigger on any and every change to the worksheet. What it does after it triggers depends on what the change was. It's all handled with If-Then statements. In it's simplest form, the code basically asks itself: "If the change that was just made meets this specific set of criteria, then execute the instructions that come next. If not, then do nothing except exit the macro". You could certainly get more complicated by using "If the change was this, then do this, but if the change was that then do this other thing, etc." You can only have one Worksheet_Change macro per sheet, but that one piece of code can perform many different actions depending on what change was just made.

Let's say you want to use Column D as the trigger column and X as the trigger string. If the code sees that an X was entered into a single cell Column D, it will run the Copy/Delete instructions. If the change was anything else, the code will simply exit.

The comments in the following macro should help explain what is happening. If you have any questions, just ask. Please be aware that this code is not going to prevent the standard Right-Click deletion of a Row. All it does is provide a method to copy the row to Sheet2 and then delete it from the original sheet. If you want to prevent the use of Right-Click--Delete, we are going to have to get into sheet protection and a more complex macro. It's doable, it just depends on how much user-proofing you are trying to obtain.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo CleanExit
'Determine if change was made to a single cell in Column D
   If Target.Cells.Count = 1 And Target.Column = 4 Then
'Determine if an X was entered
    If Target = "X" Then
'Disable events so the code doesn't fire on Deletion of Row
      Application.EnableEvents = False
'Determine next empty row on Sheet 2, Copy & Delete Target row
        nxtRw = Sheets(2).Range("D" & Rows.Count).End(xlUp).Row + 1
        Target.EntireRow.Copy Sheets(2).Range("A" & nxtRw)
        Target.EntireRow.Delete shift:=xlUp
    End If
   End If
'Enable events
      Application.EnableEvents = True
End Sub

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

Reply ↓  Report •
Related Solutions

Ask Question