Hi
This is easily done.
In your example the data is not moved. It is copied. It that what you want?
This can be done using an array formula but that is complicated and can use up a lot of memory and can be slow.
I would do this using about three macros. That would be fast and efficient.
Also what happens if you want to move the record again?
I have created something to get you started.
Have a play while I explain it.
My solution uses 3 macros.
The first is a workbook macro and runs whenever you change your selection in any sheet within the workbook.
Its main function is to stop you selecting or changing row 1 of any sheet.
It also stores the value if you select column C of "Kelly Tracking" this is so we can delete an existing entry, if you are re assigning an entry.
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Row = 1 Then Target.Offset(1, 0).Select
If ActiveSheet.Name = "Kelly Tracking" And Target.Column = 3 Then OldValue = Target.Value
End Sub
The second Macro is a sheet specific macro, all it does is check to see if you have changed a value in column C of "Kelly Tracking"
and kicks of the main macro. "MoveEntry"
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or Target.Column <> 3 Then Exit Sub
MoveEntry (Target.Row)
End Sub
We need MoveEntry because sheet specific macros cannot modify any sheet other than its own sheet.
'OldValue is defined as a public variable so we know where the record is stored.
Public OldValue As String
'CR is the Row number of the active cell, we may be able to simplify this later on and just use activecell
Sub MoveEntry(CR As Integer)
'Because your Data Validation does not match your sheet names we need to be able to convert between them.
'So create two arrays that list the Data Validation and the corresponding sheet name
NameArray1 = Array("SIF", "Dispute", "Cease", "Pnote", "SOA")
NameArray2 = Array("Kelly Tracking", "SIF Research", "Dispute Tracking", "Cease Tracking", "Prom Note", "SOA Request")
'We match the value in column c with our validation array
Pos = Application.Match(Cells(CR, 3).Value, NameArray1, 0)
'Then we read the sheet name from the sheet name array, this could be simplified.
TargetSheet = NameArray2(Pos)
'If your entry is in the target sheet then quit
If Application.CountIf(Sheets(TargetSheet).Range("B:B"), Cells(CR, 2).Value) > 0 Then Exit Sub
'Find the last used cell in colmn A
LR = Sheets(TargetSheet).Cells(Rows.Count, 1).End(xlUp).Row
'Is it really the last used cell?
10 If Cells(LR, 1).Value = "" Then LR = LR - 1: GoTo 10
'Copy your data to the first empty row
Range(Cells(CR, 1), Cells(CR, 17)).Copy Destination:=Sheets(TargetSheet).Range("A" & LR+1)
If OldValue = "" Then Exit Sub
'Find the sheet where your data is presently stored, so we can delete it
Pos = Application.Match(OldValue, NameArray1, 0)
TargetSheet = NameArray2(Pos)
'If your data is not stored there then quit
If Application.CountIf(Sheets(TargetSheet).Range("B:B"), Cells(CR, 2).Value) = 0 Then Exit Sub
'Find the cell in column B that contains your reference
Set Z = Sheets(TargetSheet).Range("B:B").Find(Cells(CR, 2).Value, LookIn:=xlValues, Lookat:=xlWhole)
'Delete that row
Sheets(TargetSheet).Rows(Z.Row).Delete Shift:=xlUp
End Sub
This is just my first pass.
I wonder where you would enter your data?
Is the Data entered into Kelly Tracking? if so you may change column C at the wrong time and move half your data.
So maybe the data in column C may need to be moved to the last column.
Or maybe we should only run MoveEntry when all the cells in the row are filled.
I await your input.
Bookmarks