Hi Anthony
Welcome to the Forum!
This code is in the attached...see if it does as you require. Let me know of issues.
Option Explicit
Sub test()
Dim Rng As Range
Dim cel As Range
Application.ScreenUpdating = False
Sheets("Sheet1").Cells.Copy
Sheets("Sheet2").Range("A1").PasteSpecial
With Sheets("Sheet2")
.Activate
.Range("A1").EntireRow.Insert
.Range("A1").Value = "Header"
.Range(("A2"), .Range("A2").End(xlDown)).AutoFilter Field:=1, Criteria1:= _
"<>*Assigned*"
Application.DisplayAlerts = False
.UsedRange.Offset(1, 0).Cells.Delete
Application.DisplayAlerts = True
.AutoFilterMode = False
Set Rng = .Range(("A2"), .Range("A2").End(xlDown))
For Each cel In Rng
cel = Split(cel.Value, ":")(1)
cel = Trim(Split(cel.Value, " - ")(0))
Next cel
End With
Application.ScreenUpdating = True
End Sub
Bookmarks