ok here's the code for the macro
Sub pick()
'
' Macro
'
Sheets("running").Select
Application.ScreenUpdating = False
Sheets("Baseline Data").Select
Range("H2:H1500").Select
Selection.Copy
Sheets("Hidden").Select
Range("A2").Select
ActiveSheet.Paste
Dim Limit As Long
Dim c As Long
Dim d As Long
Limit = Cells(Rows.Count, 1).End(xlUp).Row
d = 2
For c = 2 To Limit
If WorksheetFunction.CountIf(Range("C:C"), Cells(c, 1)) = 0 Then
Cells(d, 3) = Cells(c, 1)
d = d + 1
End If
Next c
Range("E2").Select
Selection.sort Key1:=Range("E2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("C2:D41").Select
Selection.Copy
Range("H8").Select
ActiveSheet.Paste
Range("I8").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-6]C[-5]"
Range("I8").Select
Selection.AutoFill Destination:=Range("I8:I47"), Type:=xlFillDefault
Range("I8:I47").Select
Sheets("Rolling").Select
Range("Y1:AE41").Select
Selection.Cut
Range("A43").Select
ActiveSheet.Paste
Range("A1:W41").Select
Selection.Cut
Range("I1").Select
ActiveSheet.Paste
Range("A43:G83").Select
Selection.Cut
Range("A1").Select
ActiveSheet.Paste
Range("B2:F41").Select
Selection.ClearContents
Sheets("Tier Control").Select
Range("A2:A41").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Rolling").Select
Range("B2").Select
ActiveSheet.Paste
Sheets("Tier Control").Select
Range("C2:G41").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Rolling").Select
Range("C2").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-9
Range("B2:F41").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("C2").Select
Sheets("Baseline Data").Select
Columns("H:H").Select
Selection.Copy
Sheets("tier control").Select
Columns("A:A").Select
ActiveSheet.Paste
Sheets("Baseline Data").Select
Columns("P:P").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("tier control").Select
Columns("B:B").Select
ActiveSheet.Paste
Dim ColLimit As Long
Dim RowLimit As Long
Dim co As Long
Dim r As Long
Dim PersonArray(3) As Long
RowLimit = Cells(Rows.Count, 1).End(xlUp).Row
For r = 2 To RowLimit
Cells(r, 3) = 0
Cells(r, 4) = 0
Cells(r, 5) = 0
Cells(r, 6) = 0
For co = 2 To RowLimit
If Cells(r, 1) = Cells(co, 1) Then
Select Case Cells(co, 2)
Case 0
Cells(r, 3) = Cells(r, 3) + 1
Case 1
Cells(r, 4) = Cells(r, 4) + 1
Case 2
Cells(r, 5) = Cells(r, 5) + 1
Case 3
Cells(r, 6) = Cells(r, 6) + 1
End Select
End If
Next co
Next r
For r = RowLimit To 1 Step -1
If WorksheetFunction.CountIf(Range("A1:A" & r), Range("A" & r)) > 1 Then
Rows(r).Delete shift:=xlUp
End If
Next r
Range("G2").Select
Selection.sort Key1:=Range("G2"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("H2").Select
Sheets("tier control").Select
Range("A2:A41").Select
Selection.Copy
Sheets("Fixed@Tier").Select
Range("B3").Select
ActiveSheet.Paste
Sheets("tier control").Select
Range("D2:G41").Select
Application.CutCopyMode = False
Selection.Copy
Range("G45").Select
Sheets("Fixed@Tier").Select
Range("C3").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-9
Range("B2:F43").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("A43:F43").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Range("G17").Select
Sheets("Menu").Select
End Sub