Print with macro Excel w/ sequence number

Hi there,

Here's a cheap 'n' cheerful approach which might get you moving in the right direction.

Take a look at the attached workbook and see if it does what you need. It uses the following code:

Option Explicit


Sub PrintLabels()

    Const sSHEET_NAME   As String = "Labels"
    Const sLABEL_NO     As String = "ptrLabelNo"

    Dim iNoOfLabels     As Integer
    Dim iLabelNo        As Integer
    Dim rLabelNo        As Range
    Dim wks             As Worksheet

    Set wks = ThisWorkbook.Worksheets(sSHEET_NAME)
    Set rLabelNo = wks.Range(sLABEL_NO)

    iNoOfLabels = wks.Range("ptrNoOfLabels")

    For iLabelNo = 1 To iNoOfLabels

        rLabelNo.Value = iLabelNo

'       The "Preview" argument is set to True for demonstration purposes -
'       you can change its value to False to bypass the print preview step
        wks.PrintOut Preview:=True

    Next iLabelNo

    rLabelNo.Value = 1

End Sub

The above code assumes that each label is printed on a single sheet. If you're using (e.g. 3 x 6) label sheets, the above code and the layout of the "Labels" worksheet will need to be altered accordingly. Let me know if you need any assistance with that.


Hope this helps - please let me know how you get on.

Regards,

Greg M