Excel中批量生成标签,批量打印标签。按指定模板批量打印标签

工作中经常有批量生成标签的场景，有多条数据，需要分别写到标签里面。并且，标签需要进行批量打印。

自己写了个工具，来实现这个功能。可以将三天的工作量缩减到10分钟。

生成的效果如下：

 数据源如下：

 

插件名称：速码工具箱

操作两步即可完成！

 

适用于已有数据源的情况下，一键生成多个标签。使用场景：固定资产标签、设备管理铭牌、产品生产信息标签卡等等。

插件由VBA转化而来，参考代码如下：

Sub 资产卡片()
    Rem 定义几个必要的变量，最大行数，最大列数，指定表及区域
    Dim Rm_lng As Long, Cm_int As Integer, Sh As Worksheet, Kap_Rng As Range
    Rem 卡片跳跃数，循环变量，将隔行数，卡片数组
    Dim Rs_By As Byte, i As Long, ii As Byte, RowC As Long, Arr()
    Dim New_Sh As Worksheet
    Set Sh = Worksheets("资产明细")
    With Sh
        Set Kap_Rng = Application.Intersect(.Range("a1").CurrentRegion, .UsedRange)
        With Kap_Rng
            Rm_lng = .Rows.Count
            Cm_int = .Columns.Count
        End With
    End With
    Set New_Sh = Worksheets.Add
    With New_Sh
        .Columns(1).ColumnWidth = 5.5
        .Columns(2).ColumnWidth = 9
        .Columns(3).ColumnWidth = 5.5
        .Columns(4).ColumnWidth = 9
      
    End With
    Application.ScreenUpdating = False
    Rs_By = 1 '每页1个卡片页面，3改3
    ReDim Arr(1 To 5, 1 To 2)
    Arr(1, 1) = "惠州分公司固定资产标签"
    Arr(2, 1) = "资产编号"
    Arr(3, 1) = "规格型号"
    Arr(4, 1) = "管理单位"
    Arr(5, 1) = "使用地点"

    For i = 2 To Rm_lng Step Rs_By
        RowC = RowC + IIf(i = 2, 1, 6)
        For ii = 1 To Rs_By
            Arr(2, 2) = Kap_Rng(i + ii - 1, "A").Text
            Arr(3, 2) = Kap_Rng(i + ii - 1, "C")
            Arr(4, 2) = Kap_Rng(i + ii - 1, "E")
            Arr(5, 2) = "'" & Kap_Rng(i + ii - 1, "F").Text
            With New_Sh
                With .Cells(RowC, ii + IIf(ii = 1, 0, 2))
                    .RowHeight = 30
                    .Resize(5, 2) = Arr
                    .Resize(1, 2).Merge
                    .HorizontalAlignment = xlCenter
                    .VerticalAlignment = xlCenter
                    .CurrentRegion.Borders.LineStyle = 1
                    .Offset(1, 0).Resize(5, 1).RowHeight = 20
                    .Offset(1, 0).Resize(5, 1).HorizontalAlignment = xlCenter
                    .Offset(1, 0).Resize(5, 1).VerticalAlignment = xlCenter
                    .Offset(1, 1).Resize(5, 1).HorizontalAlignment = xlLeft
                    .Offset(5, 0).RowHeight = 13
                End With
            End With
            If i = Rm_lng And i - 1 Mod 2 <> 0 Then Exit For
        Next ii
    Next i
    Application.ScreenUpdating = True
End Sub

生成好的标签，配合标签打印机，即可一键打印，非常方便。