批量通过模板打印

模版表名Template sheet25 数据列表名sheet1

打印程序

Sub PrintLabel()
    With ThisWorkbook.Sheets("Sheet1")
       limitmax = .Range("B10000").End(xlUp).Row
       For i = 2 To limitmax
           ThisWorkbook.Sheets("Template").Cells(1, 2) = .Cells(i, 2)
           ThisWorkbook.Sheets("Template").Cells(3, 1) = .Cells(i, 4)
           ThisWorkbook.Sheets("Template").Cells(3, 5) = .Cells(i, 8)
           ThisWorkbook.Sheets("Template").Cells(4, 3) = .Cells(i, 7)
           ThisWorkbook.Sheets("Template").PrintOut ActivePrinter:="DYMO LabelWriter 450 Turbo" 'Printer
           If .Cells(i, 2) = "" Then
               MsgBox "Print complete", vbInformation, Title:="Message"
               Exit Sub
           End If
       Next
    End With
    MsgBox "Print complete", vbInformation, Title:="Message"
End Sub

 

定义热键启动打印程序

Sub Auto_Open()
   ThisWorkbook.Sheets("Sheet1").Select
   Application.OnKey "%^p", "Sheet25.PrintLabel"
End Sub