Copy entire rows above a certain row that change every quarter and then hard code the copied cells

Question

I am working on a large workbook that I roll forward every quarter. I don't want to go to each worksheet and manually copy all the rows above the new inserted row. All the data in the rows above are historical data and can be hard coded, so they won't change. The new inserted row below contains formulas (starting point for this particular set of VBA code).

I need a dynamic VBA code for going up one cell, select entire row Shift+Spacebar and select entire rows above up to frozen row Shift+Ctrl+Up. Copy and paste special values to same location. Then next worksheet.

This is what I've tried:

    Sheets("Sheet1").Activate
    With Sheets("Sheets")
        
        #This part copy last row of data and insert in row below it
        .Cells(ActiveWindow.SplitRow + 1, ActiveWindow.SplitColumn + 1).Select
        Selection.End(xlDown).Select
        Selection.EntireRow.Copy
        Selection.Offset(1).Insert Shift:=xlDown
        Application.CutCopyMode = False
        
        #This part should copy the old data on top and hard code them
        Selection.Offset(-1).EntireRow.Select
        Rows("43:43").Select <---Don't want any defined cells should be dynamic based on shortcut keys
        ActiveSheet.Range(Selection, Selection.End(xlUp)).Select
        Rows("37:43").Copy  <---Don't want any defined cells should be dynamic based on shortcut keys
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
   End With

It pastes special values to cell A44 and below. Not what I want.

Answer 1

• Assumes the data table starts from Col A
• Both copy entire rows and copy / paste value consume more resource
• Below code to convert formulas to values is more efficient

Please try.

Option Explicit

Sub Formula2Val()
    Dim Sht As Worksheet
    Dim iCol As Long, c As Range
    For Each Sht In Worksheets
        iCol = Sht.UsedRange.Columns.Count
        Set c = Sht.Cells(Sht.Rows.Count, 1).End(xlUp).Offset(-1).Resize(1, iCol)
        With Sht.Range(c, c.End(xlUp))
            .Value = .Value
        End With
    Next
End Sub