请尝试以下操作：

Sub test()
    Const BLOCK_SIZE As Long = 5000
    Dim wsSrc As Worksheet, myBook As Workbook, rngCopy As Range
    
    Set wsSrc = ActiveSheet 'or some other specific sheet
    Set rngCopy = wsSrc.Rows(2).Resize(BLOCK_SIZE)
    Do While Application.CountA(rngCopy) > 0  'loop while range has content
        With Workbooks.Open("/Users/Downloads/Test/TemplateFile.xlsx") 
            rngCopy.Copy .Worksheets("Sheet1").Range("A2")
            .SaveAs "\Users\Downloads\Test\" & "R" & rngCopy.Row & "_" & .Name
            .Close SaveChanges:=True
        End With
        Set rngCopy = rngCopy.Offset(BLOCK_SIZE) 'next block down
    Loop
End Sub