Excel通过宏 实现数据的填充和表格的复制

 

Excel查看资源 --》 整改资产明细-模板

 

 

Sub 按钮1_Click()
i = 2
colNum = 13
oneRowNum = 25
a1 = "1"
SheetNo3.Range("A25:K65535").Clear

Do While 1
    '读取一行数据
    a1 = sheetNo1.Cells(i, 1) '记账凭证号  --  记账凭证编码
    a2 = sheetNo1.Cells(i, 2) '用途类型
    a3 = sheetNo1.Cells(i, 3) '卡片编号
    a4 = sheetNo1.Cells(i, 4) '资产编号
    a5 = sheetNo1.Cells(i, 5) '资产名称   -- 固定资产名称
    a6 = sheetNo1.Cells(i, 6) '规格型号
    a7 = sheetNo1.Cells(i, 7) '资产类别
    a8 = sheetNo1.Cells(i, 8) '供应商
    a9 = sheetNo1.Cells(i, 9) '2019年盘点后使用部门
    a10 = sheetNo1.Cells(i, 10) '2019年盘点后使用人
    a11 = sheetNo1.Cells(i, 11) '使用日期
    a12 = sheetNo1.Cells(i, 12) '原值
    a13 = sheetNo1.Cells(i, 13) '数量
    If a1 = "" Then
        Exit Do
    End If
        
    If i > 2 Then
        
        '拷贝生成一张新卡片
        newStartCells = 2 + oneRowNum * (i - 2)
        newEndCells = newStartCells + 23
        SheetNo3.Range("B2:K24").Copy SheetNo3.Range("B" & newStartCells & ":K" & newEndCells)
        
    End If
    '找准对应的cell填入数值
    
    SheetNo3.Cells(5 + (i - 2) * oneRowNum, 3) = a1 '固定资产名称
    SheetNo3.Cells(4 + (i - 2) * oneRowNum, 3) = a11 '记账日期
    SheetNo3.Cells(2 + (i - 2) * oneRowNum, 3) = a5 '记账凭证编码
    SheetNo3.Cells(6 + (i - 2) * oneRowNum, 3) = a6 '资产名称
    SheetNo3.Cells(6 + (i - 2) * oneRowNum, 5) = a8 '供应商
    SheetNo3.Cells(6 + (i - 2) * oneRowNum, 7) = a11 '开始使用日期
    SheetNo3.Cells(2 + (i - 2) * oneRowNum, 9) = a3 '卡片编号
    SheetNo3.Cells(4 + (i - 2) * oneRowNum, 9) = a4 '资产编号
    SheetNo3.Cells(5 + (i - 2) * oneRowNum, 6) = a8 '保管单位
    SheetNo3.Cells(5 + (i - 2) * oneRowNum, 11) = a10 '保管人
    SheetNo3.Cells(6 + (i - 2) * oneRowNum, 9) = a12 '原值
    SheetNo3.Cells(8 + (i - 2) * oneRowNum, 9) = a12 '合计
    SheetNo3.Cells(11 + (i - 2) * oneRowNum, 8) = a11  '开始日期
    SheetNo3.Cells(11 + (i - 2) * oneRowNum, 10) = a9  '保管部门
    i = i + 1
Loop
End Sub