VBA for randomizer

[bigboss89]

[MENTION][/MENTION]Hello guys I have the following code made to be a randomizer, however is not giving me the proper results that I expected, here are the results needed

1- It doesn't matter which manager name do you put it always give you the same data no matter how many rows you have on the raw data
2- Also I need the randomizer to look for the current week number and if there's no data to go back to the previous week, this I don't know how to do it so any assistance will be amazing

Attached you can find some sample data and the code is below.

Private Sub CommandButton1_Click()
Dim lr As Long, wks As Worksheet
Application.ScreenUpdating = False
Set wks = ActiveSheet
Sheets("Sheet2").Copy After:=Sheets(Sheets.Count)
With ActiveSheet
lr = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("A1:H" & lr).Sort key1:=.Range("G1"), Header:=xlYes
.Range("A1:H" & lr).AutoFilter Field:=7, Criteria1:="<>" & Sheets("Sheet1").Range("A2").Value
.Rows("1:" & lr).Delete Shift:=xlUp
lr = .Cells(.Rows.Count, "A").End(xlUp)
If lr > 5 Then
.Range("I2:I" & lr).Formula = "=RAND()"
.Calculate
.Range("I2:I" & lr).Value = .Range("I2:I" & lr).Value
wks.Range("A5:H9").Value = .Range("A1:H5").Value
Else
MsgBox "Please enter a valid login in order to continue"
End If
Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
End With
wks.Activate
wks.Range("A5:H9").Sort key1:=wks.Range("A5"), Header:=xlNo
Set wks = Nothing
Application.ScreenUpdating = True
End Sub