Move row based on dropdown selection ( and back )

[Heyho2022]

Hi Guys,

I am trying to figure out a way to move a row to another worksheet ( and back ) based on a drop down selection.
I've set up the whole spreadsheet, including the formatting and formulas but the VBA blows my mind and any help would be appreciated!

The spreadsheet is designed to track training compliance ( attached ).
I am trying to achieve an automated process where by utilising the drop down in column "C" of the "matrix" worksheet the rows will automatically move to the corresponding worksheet ( and delete from the "matrix" ).

For example, I could have a 100 rows in the matrix section marked as "active", but when one of them gets marked as "LTS" or "On Hold" I need them to automatically transfer to the "On Hold and LTS" worksheet and delete from the "matrix". The same would be applicable to the "Leaver" dropdown, which would transfer the row to the "leavers" worksheet.

There are a few "difficulties" though:

1. I need to be able to move them back - I'd love to achieve that in the same process, but in reverse, for example:
   1. Employee was active, but had to be moved to the "LTS and On Hold" worksheet. After a while they need to be moved back to the "matrix" worksheet and I'd like to achieve that by changing the dropdown from "LTS" or "On Hold" to "Active" ( within the "LTS and On Hold" worksheet ). This would then transfer them back to matrix and delete from the LTS and On Hold
2. The copied/transferred row must fall into the table and into the first empty row, In my case, the table starts at A7:
   1. If A7 is empty, then copy there
   2. If A7 contains data, copy to the next empty row etc.
3. Each training column in fact consists of 2 columns, one for data entry ( date ) and the second is formulated to give an expiry date. the second column doesn't require any user input. My concerns are that If the entire row is copied over, this will likely impact the existing formulas. A few solutions could be:
   1. I am totally wrong and the formulas won't be affected if data is copied over them
   2. The VBA code will only copy over the "user entry" columns i.e H, J, L etc leaving the formula to do the rest for the I, K, M columns
   3. No idea - would love some suggestions!

For reference, this is the layout of the spreadsheet I am working with ( Each sheet is exactly the same and columns go al the way to AU ):

Any help would be greatly appreciated!


Thank you

 

[Heyho2022]

Hi Guys,

So I figured out that pasting over cells with formula doesn't erase the formula - great.
This means I only need to accomplish the move of the whole row based on the dropdown value ( and back )

Would love to hear some feedback on this!


Thank you

 

[Heyho2022]

Hi... again!

Can't see an edit button hence the multiple posts.
I've re read the initial post and I think it is confusing, so though I would summarise my end goal:

Matrix Worksheet:

If column "C" = "Active" then no action
If column "C" = "LTS" or "On Hold" move the row to worksheet "On Hold and LTS"
If column "C" = "Leaver" move the row to "Leavers" worksheet

LTS and On Hold Worksheet:

If column "C" = "Active", move to Matrix worksheet
If column "C" = "LTS" or "On Hold" then no action


Any transfer of rows, regardless of which way around must start at row "A7" to keep the layout of the table, then to the next empty row.
If transferring the row back to the "matrix" worksheet, transfer to the first empty row.


Hope this makes it clearer

 

[Heyho2022]

Another update

I've used the below code on the main "Matrix" worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)

' Check to see only one cell updated
If Target.CountLarge > 1 Then Exit Sub

' Check to see if entry is made in column A after row 6 and is set to "Yes"
If Target.Column = 3 And Target.Row > 6 And (Target.Value = "LTS" Or Target.Value = "On Hold") Then
Application.EnableEvents = False
' Copy columns B to I to complete sheet in next available row
Range(Cells(Target.Row, "A"), Cells(Target.Row, "AU")).Copy Sheets("On Hold and LTS").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
' Delete current row after copied
Rows(Target.Row).Delete
Application.EnableEvents = True
End If

End Sub


It works great, but for the life of me I can't figure out how to reverse the action?