Here is the first part, you should be able to get the other parts figured out with this.
Make sure to clear Interview and cancel cells before running initial code.
Click button on attached.
Sub Interview_cancel()
Dim sh As Worksheet
Dim rw As Long, rng As Range, c As Range
Set sh = Tabelle1
With sh
rw = .Range("A5").End(xlDown).Row
Set rng = Range("H6:I" & rw).SpecialCells(xlCellTypeConstants, 2)
For Each c In rng.Cells
If c = "x" Then
If .Cells(5, c.Column) = "Interview?" Then
.Range(.Cells(c.Row, "A"), .Cells(c.Row, "C")).Copy .Range("A31").End(xlUp).Offset(1, 0)
Else
.Range(.Cells(c.Row, "A"), .Cells(c.Row, "B")).Copy .Range("A50").End(xlUp).Offset(1, 0)
End If
End If
.Range(.Cells(c.Row, "A"), .Cells(c.Row, "I")).ClearContents
Next
.Range("A5:I" & rw).Sort key1:=Range("A5"), order1:=xlAscending, Header:=xlYes
End With
End Sub
Bookmarks