Excel多工作表数据复制VBA问题：仅写入第14行忽略后续行

问题根源 #

你的代码只处理了Invoice表的第14行，没有对14-22行做循环遍历，导致后续行的数据未被写入Inctype表。

修正后的完整代码 #

Sub WriteInvoiceData()
    Dim wsInvoice As Worksheet, wsRecOfInv As Worksheet, wsInctype As Worksheet
    Dim lastRowRec As Long, lastRowInc As Long
    Dim i As Long
    Dim invNum As String, invDate As Date, custName As String
    Dim category As String ' 需替换为category实际所在单元格，比如"J8"
    
    ' 绑定工作表对象
    Set wsInvoice = ThisWorkbook.Worksheets("Invoice")
    Set wsRecOfInv = ThisWorkbook.Worksheets("RecOfInv")
    Set wsInctype = ThisWorkbook.Worksheets("Inctype")
    
    ' 提取Invoice表固定字段值
    invNum = wsInvoice.Range("H3").Value
    invDate = wsInvoice.Range("H4").Value
    custName = wsInvoice.Range("C8").Value
    category = wsInvoice.Range("XX").Value ' 替换为category实际单元格地址
    
    ' 写入RecOfInv表
    lastRowRec = wsRecOfInv.Cells(wsRecOfInv.Rows.Count, "A").End(xlUp).Row + 1
    With wsRecOfInv
        .Cells(lastRowRec, "A") = invNum
        .Cells(lastRowRec, "B") = invDate
        .Cells(lastRowRec, "C") = custName
        .Cells(lastRowRec, "D") = wsInvoice.Range("I24").Value
        .Cells(lastRowRec, "E") = category
    End With
    
    ' 循环遍历14-22行，写入Inctype表
    lastRowInc = wsInctype.Cells(wsInctype.Rows.Count, "A").End(xlUp).Row + 1
    For i = 14 To 22
        ' 跳过空行（可选，根据实际需求调整）
        If wsInvoice.Range("B" & i).Value <> "" Then
            With wsInctype
                .Cells(lastRowInc, "A") = invNum
                .Cells(lastRowInc, "B") = invDate
                .Cells(lastRowInc, "C") = custName
                .Cells(lastRowInc, "D") = wsInvoice.Range("B" & i).Value ' 编码
                .Cells(lastRowInc, "E") = wsInvoice.Range("H" & i).Value ' 数量
                .Cells(lastRowInc, "F") = wsInvoice.Range("I" & i).Value ' 单项金额
            End With
            lastRowInc = lastRowInc + 1 ' 更新下一个写入行号
        End If
    Next i
    
    ' 释放对象
    Set wsInvoice = Nothing
    Set wsRecOfInv = Nothing
    Set wsInctype = Nothing
End Sub

关键修改说明 #

• 新增For i = 14 To 22循环，覆盖目标行范围
• 用lastRowInc变量动态跟踪Inctype表的下一个空行，避免覆盖已有数据
• 循环内复用H3、H4、C8的固定值，同时提取当前行的B、H、I列数据
• 加入空行判断逻辑，可根据实际业务需求保留或删除

内容的提问来源于stack exchange，提问作者Gideon De Klerk