【原】Excel VBA【案例】生成出库单：根据销售明细表自动生成出库单

快速浏览

往期合集：【2023年3月】【2023年4月】【2023年5月】【2023年6月】【2023年7月】【2023年8月】【2023年9月】【2023年10月】

实用案例

|日期控件||简单的收发存||收费管理系(Access改进版)|

|电子发票管理助手||电子发票登记系统(Access版)|

|文件合并||表格拆分||审计凭证抽查底稿|

|中医诊所收费系统(Excel版)||中医诊所收费系统(Access版)||银行对账单自动勾对|

收费使用项目

|财务管理系统||工资薪金和年终奖个税筹划|

内容提要

• 字典的用法
• 多重循环给数组赋值
• 工作表添加分页符

大家好，我是冷水泡茶，今天在论坛上看到一个求助贴：

[求助] 如何根据销售明细表自动生成出仓单，酬谢！

求助各位老师，如何根据销售明细表同一个出仓单号自动生成一张出仓单，138个出仓单号自动生成138张出仓单，要求行高和打印格式跟模板一样，酬谢！

他的数据表是这样的：

销售明细表：

模板表：

生成出仓单表，看样子是要连续分页打印，但他这个表设置的分页位置肯定是不对的，跟他的模板不一样，应该要统统清除掉再根据模板来设置：

基本思路：

1、把销售明细表装入数组arr，通过循环，把“出仓单号+日期+收货单位”以“|”连接起来，作为字典的key，模板中的必需字段存入一个数组arrTem，作为字典的item。

2、通过循环字典的item，计算所需打印的总页数，这里虽然他的每个出库单的明细条目数均小于模板的行数，即一个单号只会有一个出库单，但是我觉得我们还是需要考虑一个单号的明细条目超过模板的总条目数的情况，即一个单号会有2张以上的出库单。

3、根据总的出库单数量，复制“模板”到“生成出仓单“表，这里我们先要把模板的格式手工设置好，字体啊，对齐啊，省得在代码里设置比较麻烦，修改也不方便。

4、在复制模板的时候，每一张出库单添加一个分页符。

5、把复制好模板格式后，再把“生成出仓单“表装入数据arrTem，准备写入数据。向数组写入数据比向工作表要快很多。

6、循环字典的key，把key以“|”分列到数组arrStr，把对应的元素写入数组中单据表头项目；把item装入数组arrItem，把明细项目写入数组表体位置。

演示效果：

VBA代码

1、在模块1里，BillGenerator过程：

Sub BillGenerator()    Dim ws As Worksheet, rng As Range, startCell As Range, endCell As Range    Dim arr(), arrTem(), iRows As Integer, iCols As Integer, iPages As Integer    Dim dic As Object, dKey As String, arrItem()    Dim totalPages As Integer, arrStr() As String    Set ws = ThisWorkbook.Sheets("销售明细表")    Set dic = CreateObject("Scripting.Dictionary")    arr = ws.UsedRange    For i = 2 To UBound(arr)        dKey = arr(i, 2) & "|" & arr(i, 3) & "|" & arr(i, 4)        If Not dic.exists(dKey) Then  '如果dkey不存在            k = 0        Else            arrTem = dic(dKey)            k = UBound(arrTem, 2) + 1        End If        ReDim Preserve arrTem(0 To 6, 0 To k)        For j = 0 To 6            arrTem(j, k) = arr(i, j + 5)        Next        dic(dKey) = arrTem   '把数组存入字典    Next    For Each Item In dic.items        totalPages = totalPages + Application.WorksheetFunction.RoundUp((UBound(Item, 2) + 1) / 15, 0)    Next    Set ws = ThisWorkbook.Sheets("生成出仓单")    Set rng = ThisWorkbook.Sheets("模板").Range("A1:H22")    iRows = rng.Rows.Count    iCols = rng.Columns.Count    With ws        .Cells.Clear        .ResetAllPageBreaks        For i = 1 To totalPages            Set startCell = .Range("A1").Offset((i - 1) * iRows, 0)            Set endCell = startCell.Offset(rng.Rows.Count - 1, iCols - 1)            rng.Copy Destination:=startCell            .HPageBreaks.Add before:=endCell.Offset(1, 0)        Next        .PageSetup.FitToPagesWide = 1        arrTem = .UsedRange    End With    p = 0    For Each Key In dic.keys        arrStr = Split(Key, "|")        arrItem = dic(Key)        iPages = Application.WorksheetFunction.RoundUp((UBound(arrItem, 2) + 1) / 15, 0)        For i = 1 To iPages            arrTem(3 + p * iRows, 7) = arrStr(0)            arrTem(4 + p * iRows, 7) = arrStr(1)            arrTem(4 + p * iRows, 2) = arrStr(2)            arrTem(3 + p * iRows, 8) = "第" & i & "/" & iPages & "页"            For j = (i - 1) * 15 To Application.WorksheetFunction.Min(UBound(arrItem, 2), i * 15 - 1)                For k = 0 To UBound(arrItem)                    arrTem(6 + p * iRows + j - (i - 1) * 15, k + 2) = arrItem(k, j)                Next                arrTem(21 + p * iRows, 5) = arrTem(21 + p * iRows, 5) + Val(arrItem(3, j))                arrTem(21 + p * iRows, 7) = arrTem(21 + p * iRows, 7) + Val(arrItem(5, j))            Next            p = p + 1        Next    Next    ws.Cells(1, 1).Resize(UBound(arrTem), UBound(arrTem, 2)) = arrTem    MsgBox "Done!"    ws.ActivateEnd Sub

代码解析：

（1）line2~5，定义变量。

（2）line9~22，处理数据，把每一个单号+日期+单位作为字典的key，把对应的明细据装入数组arrTem，作为字典的item。

（3）line23~25，循环数字典的item，计算总页数，用item的最大列标加上1再除以15（模板的行数），向上取整，就是该出库单的页数。

（4）line26~41，根据总页数，把模板复制到“生成出仓单“表中，每页插入分页符，页面设为1页宽，再把“生成出仓单”表装入数据arrTem，准备写入明细数据。

（5）line43~61，循环字典的key，先对key进行分列，循环该单号的页数，把单号、日期、单位填入表头位置；再把明细数据填入相应位置，接着计算数量、金额的合计数。

（6）line62，把arrTem再回写到目标工作表。

2、在Sheet(销售明细表)里，CmdGenerate命令按钮：

Private Sub CmdGenerate_Click()    Call BillGeneratorEnd Sub

代码解析：调用BillGenerator过程。

后记

1、根据模板打印明细数据，也可以采用把明细数据写入模板，直接循环打印出来，比如我们的【凭证打印】。

2、今天代码虽然不多，但是在循环写入数据的时候，还是费了点脑筋。

3、根据指定行数插入分页符，还是比较有用的，如果不给他插入分页符，那么生成的出库单打印起来就不太好办了。现在我们给他设置了，就可以一键打印。

ws.HPageBreaks.Add before:=endCell.Offset(1, 0)

好，今天就到这，我们下期再会。

~~~~~~End~~~~~~

	安利小店 安利的牙膏非常不错，用了以后就不想再用其他的了；洗洁精、洗衣液也是日常必备，用过都说好！
	合谷医疗 合谷医疗专攻各种疑难杂症，尤其擅长腰颈椎疾病、儿童神经发育异常、多动症、自闭孤独症治疗，可谓神乎其技！体验过的直呼早点来就好了！

喜欢就点个赞、点在看、留言评论、分享一下呗！感谢支持！

案例文件分享说明：

• 案例文件可免费分享，但需符合以下要求：
  

• 请关注、点赞、点在看、点广告、留言，方便的话分享一下就完美啦！如果不便走上面的“流程”，请打赏，万分感谢！