Dynamic Auto-Fill Range based upon Specific number of rows

Hi - I am trying to create a macro that will select the last row of cells in column CA:EH and drag them down through the previous quarter end. To do this I have created a function to get the number of rows the data must be pulled down called weekCount.

However, I am having an issue with coding the AutoFill range. I think that it's getting confused between row or select. I'm also not sure that I formatted the AutoFill range correctly for this scenario. Can someone please take a look at the code below?

Sub Update()
Dim zRow As String
Dim dropspot As String
Dim firstrow As String
zRow = "CA7"

      
'brings us to last cell with values in column CA
    Range(zRow).Select
    Range(Selection, Selection.End(xlDown)).Select
    dropspot = Range(zRow).End(xlDown).Select
    
'selects the portion of data that must be computed for the recent quarter
    firstrow = Range(Selection, Selection.End(xlToRight)).Select
    
'auto-fills cells downward
    Selection.AutoFill Destination:=Range("CA:EH" & weekCount.row), Type:=xlFillDefault
    
'updates numbers in dragged down cells
    Calculate
    
'copies and pastes special values data from new pulled down cells except for last row
'need to make these ranges dynamic still
    Range("CA1734").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("CA1734:EH1741").Select
       Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
End Sub