Sub AmendRows()

    Dim worksheet1 As Worksheet, worksheet2 As Worksheet
    Dim n As Long, rng As Range
    
    Set worksheet1 = Sheet1 ' as appropriate
    Set worksheet2 = Sheet2 ' as appropriate
    
    n = worksheet2.UsedRange.Rows.Count - 1 ' no header
    If n < 1 Then Exit Sub
   
    With worksheet1

        With .Rows("5:" & .Rows.Count)
            .ClearContents
            .ClearFormats
        End With
        
        Set rng = .Range("A4:V4")
        rng.AutoFill Destination:=rng.Resize(n)
        
    End With
    MsgBox n & " Rows Amended! " & rng.Resize(n).Address

End Sub

向下复制公式

Option Explicit

Sub AmendRows()

    Const DST_FIRST_ROW As String = "A4:V4"
    Const SRC_FIRST_CELL As String = "A2"

    Application.ScreenUpdating = False
    'Application.DisplayAlerts = False ' ?
    
    Locktabs False
    
    Dim srCount As Long

    With worksheet2.Range(SRC_FIRST_CELL)
        Dim slCell As Range: Set slCell = .Resize(.Worksheet.Rows.Count - .Row _
            + 1).Find("*", , xlFormulas, , , xlPrevious)
        If Not slCell Is Nothing Then srCount = slCell.Row - .Row + 1
    End With
    
    With worksheet1.Range(DST_FIRST_ROW)
        With .Offset(1).Resize(.Worksheet.Rows.Count - .Row)
            .ClearContents
            .ClearFormats
        End With
        If srCount > 0 Then .Copy .Resize(srCount)
    End With
    
    Locktabs True
    
    If Not worksheetmain Is ActiveSheet Then
        With worksheetmain
            If Not .Parent Is ActiveWorkbook Then .Parent.Activate
            .Select
        End With
    End If

    'Application.DisplayAlerts = True ' ?
    Application.ScreenUpdating = True
    
    MsgBox "Rows Amended!", vbInformation

End Sub