Relative Selection

I have a function that creates a copy of the current worksheet, renames the worksheet to the current date (MMM-YY) and changes volatile functions in certain cells to static functions to effectively freeze the data or create a snapshot. The range for the data that needs to be frozen is S8:S300.

The worksheet formula is as follows:

S8=INT((TODAY()-R8)/30.14) where R8 is the Start Date for an individual. This spits out an integer value for how many months a person has been in a position.

I need my VBA sub to replace the above function with a static integer. Where S8 references R8, S10 references R10, and so on and so forth all the way to S300. Below is a copy of my "Freeze Data" sub that attempts to do this. Since I'm using a variable that does the calculation I don't know how to reference multiple ranges relative to the output ranges. I have also attached the excel sheet if any 42A's out there are lurking and want a graphical leader slate.

Public Sub CommandButton1_Click()
    Dim newName As String
    Dim staticDate As Integer

    newName = Format(Date, "mmm-yy")
    staticDate = (Date - Worksheets("MAIN SLATE").Range("R8")) / 30.14

    If newName <> "" Then
        ''''''''Copy Main Slate''''''''
        ActiveSheet.Copy After:=Worksheets(Sheets.Count)
        ''''''''Change Copy Name to Current MMM-YY''''''''
        ActiveSheet.Name = newName
        ''''''''Deletes Freeze Data Button''''''''
        Worksheets(newName).Shapes("CommandButton1").Delete
        ''''''''Change "Time In Pos" to static Integer''''''''
        Worksheets(newName).Range("S8:S300").Value = staticDate
        ''''''''Change cell "T4" to static date'''''''''
        Worksheets(newName).Range("T4").Value = newName
    End If
End Sub

Any help would be appreciated. Apologies if this has been covered already as I am unsure of how to really describe the problem.