中燃料场报表生成器--入库报表

最新推荐文章于 2024-08-30 12:05:45 发布

HappyDelano

最新推荐文章于 2024-08-30 12:05:45 发布

阅读量192

点赞数

分类专栏： VBA

本文链接：https://blog.csdn.net/liu_zhen_wei/article/details/83149248

版权

VBA 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

Option Explicit

Sub CmdGroup2()

 ' 判断当前数据表是否为进销存的销售明细表
    
    If Range("A1") <> "进货明细表" Then
        MsgBox "当前数据表不是 《进货明细表》 或者已经被修改，请确认！"
        End '结束程序的运行
    End If
    
    
    ' 新建一个数据表,位于Sheet1后面

    
    If Sheets(Sheets.Count).Name = "料场入库明细" Then
        MsgBox "料场入库明细 数据表已经存在，删除后可重新创建"
        End
    End If

    Sheets.Add After:=Sheets(1)
    ActiveWorkbook.ActiveSheet.Name = "料场入库明细"
    
    
    '合并后居中单元格
    Range("A1:N1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
    End With
    Selection.Merge
    
    Range("A1") = "材料入库明细表"
    
    
    '填写表头
    Range("A2") = "序号"
    Range("B2") = "入库日期"
    Range("C2") = "纸质出库单编号"
    Range("D2") = "采购网出库单编号"
    Range("E2") = "物资编码"
    Range("F2") = "物资名称"
    Range("G2") = "单位"
    Range("H2") = "入库数量"
    Range("I2") = "含税单价"
    Range("J2") = "含税金额"
    Range("K2") = "其它费用"
    Range("L2") = "供应商"
    Range("M2") = "库房名称"
    Range("N2") = "备注"
    
    '设置表头格式
    Rows("1:1").RowHeight = 22.5
    Range("A1:N1").Font.Size = 18
    Range("A2:N2").Font.Size = 14
    Range("A2:N2").Font.Bold = True
    
    With Range("A2:N2").Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0.499984740745262
        .PatternTintAndShade = 0
    End With
    With Range("A2:N2").Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    
    '根据单元格的内容自动调整单元格大小
    Cells.EntireColumn.AutoFit
    Cells.EntireRow.AutoFit
    
    '查看销售明细表一共记录了多少行
    Dim mItemCount As Integer
    mItemCount = ActiveWorkbook.Sheets(1).UsedRange.Rows.Count
    
    '需要的数据为第13行~mItemCount-1行，复制到对应的表中
    ActiveWorkbook.Sheets(1).Range(ActiveWorkbook.Sheets(1).Cells(13, 2), ActiveWorkbook.Sheets(1).Cells(mItemCount - 1, 2)).Copy (ActiveSheet.Range("C3"))   '单据编号
    ActiveWorkbook.Sheets(1).Range(ActiveWorkbook.Sheets(1).Cells(13, 23), ActiveWorkbook.Sheets(1).Cells(mItemCount - 1, 23)).Copy (ActiveSheet.Range("B3"))   '单据日期
    ActiveWorkbook.Sheets(1).Range(ActiveWorkbook.Sheets(1).Cells(13, 6), ActiveWorkbook.Sheets(1).Cells(mItemCount - 1, 6)).Copy (ActiveSheet.Range("E3"))   '物资编码
    ActiveWorkbook.Sheets(1).Range(ActiveWorkbook.Sheets(1).Cells(13, 4), ActiveWorkbook.Sheets(1).Cells(mItemCount - 1, 4)).Copy (ActiveSheet.Range("F3"))   '名称
    ActiveWorkbook.Sheets(1).Range(ActiveWorkbook.Sheets(1).Cells(13, 10), ActiveWorkbook.Sheets(1).Cells(mItemCount - 1, 10)).Copy (ActiveSheet.Range("G3")) '单位
    ActiveWorkbook.Sheets(1).Range(ActiveWorkbook.Sheets(1).Cells(13, 12), ActiveWorkbook.Sheets(1).Cells(mItemCount - 1, 12)).Copy (ActiveSheet.Range("H3")) '数量
    ActiveWorkbook.Sheets(1).Range(ActiveWorkbook.Sheets(1).Cells(13, 14), ActiveWorkbook.Sheets(1).Cells(mItemCount - 1, 14)).Copy (ActiveSheet.Range("I3")) '单价
    ActiveWorkbook.Sheets(1).Range(ActiveWorkbook.Sheets(1).Cells(13, 15), ActiveWorkbook.Sheets(1).Cells(mItemCount - 1, 15)).Copy (ActiveSheet.Range("J3")) '金额
    ActiveWorkbook.Sheets(1).Range(ActiveWorkbook.Sheets(1).Cells(13, 20), ActiveWorkbook.Sheets(1).Cells(mItemCount - 1, 20)).Copy (ActiveSheet.Range("L3"))   '来往单位
    ActiveWorkbook.Sheets(1).Range(ActiveWorkbook.Sheets(1).Cells(13, 17), ActiveWorkbook.Sheets(1).Cells(mItemCount - 1, 17)).Copy (ActiveSheet.Range("M3"))   '库房名称
    
    
    '填写序号
    Dim i As Integer
    For i = 3 To mItemCount - 11 Step 1
        Cells(i, 1) = i - 2
    Next i
    


End Sub