Sheet Rank

'
'
Private Sub Worksheet_Activate()
Worksheets("rank").Unprotect "1"
Application.ScreenUpdating = False
'======copy dari dkn ke sheet rank==============
    Worksheets("rank").Range("G16:AJ65").ClearContents
    Worksheets("dkn").Range("nilaidkn").Copy
    Sheets("rank").Range("G16").Select
    Selection.PasteSpecial paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    
'======copy RATA-RATA GANJIL dari DKN jika semester genap ==============
  
    Worksheets("dkn").Range("rt2ganjil").Copy
    Sheets("rank").Select
    Range("AL16").Select
    Selection.PasteSpecial paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
       
    Range("G16").Select
    
    
   
   
   
    Worksheets("dkn").Range("rt2ganjil").Copy
    Sheets("rank").Select
    Range("AL16").Select
    Selection.PasteSpecial paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
       
    Range("G16").Select



'=========sort mengurutkan peringkat===============
Range("G16:AN65").Select
    ActiveWorkbook.Worksheets("rank").sort.SortFields.Clear
    ActiveWorkbook.Worksheets("rank").sort.SortFields.Add Key:=Range("AN16:AN65") _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("rank").sort.SortFields.Add Key:=Range("L16:L65"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("rank").sort.SortFields.Add Key:=Range("M16:M65"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("rank").sort.SortFields.Add Key:=Range("N16:N65"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("rank").sort.SortFields.Add Key:=Range("O16:O65"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("rank").sort.SortFields.Add Key:=Range("P16:P65"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("rank").sort.SortFields.Add Key:=Range("Q16:Q65"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("rank").sort.SortFields.Add Key:=Range("R16:R65"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("rank").sort.SortFields.Add Key:=Range("S16:S65"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("rank").sort.SortFields.Add Key:=Range("T16:T65"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("rank").sort.SortFields.Add Key:=Range("U16:U65"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("rank").sort.SortFields.Add Key:=Range("V16:V65"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("rank").sort.SortFields.Add Key:=Range("W16:W65"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("rank").sort.SortFields.Add Key:=Range("X16:X65"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("rank").sort.SortFields.Add Key:=Range("Y16:Y65"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("rank").sort.SortFields.Add Key:=Range("Z16:Z65"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("rank").sort.SortFields.Add Key:=Range("AA16:AA65") _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("rank").sort.SortFields.Add Key:=Range("AB16:AB65") _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("rank").sort.SortFields.Add Key:=Range("AC16:AC65") _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("rank").sort.SortFields.Add Key:=Range("AD16:AD65") _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("rank").sort.SortFields.Add Key:=Range("AE16:AE65") _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("rank").sort.SortFields.Add Key:=Range("AF16:AF65") _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("rank").sort.SortFields.Add Key:=Range("AG16:AG65") _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("rank").sort.SortFields.Add Key:=Range("AH16:AH65") _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("rank").sort.SortFields.Add Key:=Range("AI16:AI65") _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("rank").sort.SortFields.Add Key:=Range("AJ16:AJ65") _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("rank").sort
        .SetRange Range("G16:AN65")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
  
ActiveWindow.Zoom = 100
ActiveWindow.ScrollColumn = 1
    





'========mengatur lebar kolom ==============
Columns("H:I").EntireColumn.AutoFit
Columns("M:AJ").EntireColumn.AutoFit
Columns("J:K").Hidden = True


'===menyembunyikan kolom GANJIL TAHUN LALU saat diklik Genap==
If Worksheets("data").Range("P7").Value = "Ganjil" Then
Columns("AL").Hidden = True
Else
Columns("AL").Hidden = False
End If


'
'
'Dim barisakhir3 As Integer
'
'barisakhir3 = Range("G13").Value
'Rows(barisakhir3 & ":65").Select
'Selection.EntireRow.Hidden = True
'
'
''======menyembunyikan kolom yang sisa======
'
'Columns(Range("I13").Value & ":AJ").Select
'Selection.EntireColumn.Hidden = True
'
'
'Columns("U:AJ").Select
'    Selection.EntireColumn.Hidden = True
'======mengatur tampilan=======
ActiveWindow.Zoom = 90
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1
Application.ScreenUpdating = True
Worksheets("rank").Protect "1", userinterfaceonly:=True
End Sub