Excel VBA复制粘贴功能异常求助：无法粘贴行至新建工作簿

核心错误原因 #

原代码中newSheet.Rows(newSheet.Rows.Count + 1).PasteSpecial xlPasteValues语句的问题在于：

• Excel工作表的最大行号是newSheet.Rows.Count（如Excel 2007+版本为1048576），Rows.Count + 1会超出合法行范围，直接触发运行错误。
• 未定位到新工作表中实际有数据的最后一行，应该从已用行的下一行开始粘贴，而非直接用总行数+1。

完整修复后的代码 #

Sub CopyRowsWithHaynes()
    ' 创建存储数据的新工作簿
    Dim newWorkbook As Workbook
    Set newWorkbook = Workbooks.Add
    
    ' 创建新工作表并命名
    Dim newSheet As Worksheet
    Set newSheet = newWorkbook.Sheets.Add
    newSheet.Name = "Haynes Rows"
    
    ' 指定要搜索的文件夹路径
    Dim folderPath As String
    folderPath = "C:\Excel Files"
    
    ' 获取文件夹下所有Excel文件
    Dim file As String
    file = Dir(folderPath & "\*.xl*")
    
    ' 记录新工作表的已用最后一行（初始为第一行）
    Dim newLastRow As Long
    newLastRow = 1
    
    Do While file <> ""
        ' 打开源工作簿
        Dim sourceWorkbook As Workbook
        Set sourceWorkbook = Workbooks.Open(folderPath & "\" & file)
        
        ' 遍历源工作簿的所有工作表
        For Each sourceSheet In sourceWorkbook.Sheets
            Dim lastRow As Long
            ' 获取F列最后一行数据行号
            lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "F").End(xlUp).Row
            
            Dim i As Long
            For i = 1 To lastRow
                Dim cellValue As String
                cellValue = Trim(sourceSheet.Cells(i, "F").Value)
                
                ' 改用InStr判断是否包含"Haynes"，避免单元格内容过短时Left函数报错
                If InStr(1, UCase(cellValue), "HAYNES") > 0 Then
                    ' 复制源行值到新工作表的目标行
                    sourceSheet.Rows(i).Copy
                    newSheet.Rows(newLastRow).PasteSpecial xlPasteValues
                    ' 更新新工作表的已用最后一行
                    newLastRow = newLastRow + 1
                End If
            Next i
        Next sourceSheet
        
        ' 关闭源工作簿，不保存修改
        sourceWorkbook.Close SaveChanges:=False
        ' 获取下一个文件
        file = Dir()
    Loop
    
    ' 自动调整列宽
    newSheet.Columns.AutoFit
End Sub

额外优化点 #

• 新增newLastRow变量，精准记录新工作表的已用行位置，确保粘贴位置正确。
• 替换原Left判断逻辑为InStr，避免单元格内容长度不足6字符时触发错误，同时支持"Haynes"出现在单元格任意位置的场景。
• 添加sourceWorkbook.Close语句，处理完源文件后及时关闭，避免内存占用过高。

内容的提问来源于stack exchange，提问作者이정민