Option Explicit

Sub CreateUniqueList()

Dim lastrow As Long

ActiveSheet.Name = "Raw Data"

Dim ws As Worksheet
Set ws = Sheets("Raw Data")

Dim Champs As Worksheet
Set Champs = ActiveWorkbook.Sheets.Add(After:= _
    ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count))
Champs.Name = "Unique Champions"

ws.Activate

lastrow = Cells(Rows.Count, "D").End(xlUp).Row

    ActiveSheet.Range("D6:D" & lastrow).AdvancedFilter _
    Action:=xlFilterCopy, _
    CopyToRange:=Champs.Range("A1"), _
    Unique:=True

Dim Champ1 As String
Dim Champ2 As String
Dim Champ3 As String
Dim Champ4 As String
Dim Champ5 As String
Dim Champ6 As String
Dim Champ7 As String
Dim Champ8 As String
Dim Champ9 As String
Dim Champ10 As String
Dim Champ11 As String
Dim Champ12 As String
Dim Champ13 As String
Dim Champ14 As String
Dim Champ15 As String

Champs.Activate

Champ1 = Cells(2, 1).Value
Champ2 = Cells(3, 1).Value
Champ3 = Cells(4, 1).Value
Champ4 = Cells(5, 1).Value
Champ5 = Cells(6, 1).Value
Champ6 = Cells(7, 1).Value
Champ7 = Cells(8, 1).Value
Champ8 = Cells(9, 1).Value
Champ9 = Cells(10, 1).Value
Champ10 = Cells(11, 1).Value
Champ11 = Cells(12, 1).Value
Champ12 = Cells(13, 1).Value
Champ13 = Cells(14, 1).Value
Champ14 = Cells(15, 1).Value
Champ15 = Cells(16, 1).Value

End Sub

lastrow = Cells(Rows.Count, "D").End(xlUp).Row

ActiveSheet.Range("D6:D" & lastrow).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Champs.Range("A1"), _
Unique:=True