Rob_010101
Board Regular
- Joined
- Jul 24, 2017
- Messages
- 198
- Office Version
- 365
- Platform
- Windows
I am working with an absence tracker and am currently using the below VB Code so when a drop-down list item returns "Closed", it copies the row across to the "Archived Absence" sheet.
In the drop down, there is also a selection "LTS" for Long Term Sick people. When "LTS" is selected, I need it to copy the row over to the "Long Term" sheet.
In short:
- if "closed" is selected, it moves to "Archived absence"
- if "LTS" is selected, it moves to "Long Term"
The above code has been designed to include when filters are applied, to make sure the moving does not overwrite anything.
My issue is getting the code to do both (I have no knowledge of VB and I got the above by asking on forums).
Regards
In the drop down, there is also a selection "LTS" for Long Term Sick people. When "LTS" is selected, I need it to copy the row over to the "Long Term" sheet.
In short:
- if "closed" is selected, it moves to "Archived absence"
- if "LTS" is selected, it moves to "Long Term"
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim fromRow%, archiveRow%, archiveList As Worksheet
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Target, Range("O2:O500000")) Is Nothing Then 'amend this range address to your
Set archiveList = ThisWorkbook.Worksheets("Archived Absence")
If Target.Value = "Closed" Then
fromRow = ActiveCell.Row
With archiveList
If .FilterMode Then
Dim strMatch As String
strMatch = "match" & Replace("(2,1/(a:a>""""),1)", "a:a", .AutoFilter.Range.Cells(1).EntireColumn.Address(0, 0, 1, 1))
archiveRow = Evaluate(strMatch) + 1
Else
archiveRow = archiveList.Cells(archiveList.Rows.Count, 1).End(3).Row + 1
End If
End With
Range(Cells(fromRow, 1), Cells(fromRow, 15)).Copy archiveList.Cells(archiveRow, 1)
Rows(fromRow).EntireRow.Delete
End If
End If
End Sub
The above code has been designed to include when filters are applied, to make sure the moving does not overwrite anything.
My issue is getting the code to do both (I have no knowledge of VB and I got the above by asking on forums).
Regards