Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Thanked 0 Times in 0 Posts

    Print Loop (2007)

    I am looking for some help on creating a Print Loop based on a changing number of lines resulting from a daily pivot table.

    In short, I am creating a Bill Of Lading for shipments leaving my warehouse. Today, I might have 30 shipments that I would like to automatically print-all with a Macro. Tomorrow, the number of shipments will most likely change.

    I have set up my print area template that is Vlookup'ing information from a pivot table. Each row of the pivot table has a "reference number" (first row is 1, the second is 2, etc). I would like to have a macro that would start from row one. Enter the "reference number" into the template depending on the total number of pivoted rows, vlookup (good with that part) the data, print the BOL and repeat the process IF there are additional rows in the PIVOT table.

    I have attached a pretty basic concept of the BOL.

    The Blue are is the template
    Column I- indicates the "reference number" that is to be copied to cell B3, and then templated printed.
    I am in need of a print loop that would then go back to the pivot table, check to make sure there is a data in reference 2 and if so bring it back to the template, paste and print... and repeat.

    In this file I show 5 rows of results from a pivot (pivot not done in this file)
    I have a continuous number for 1 through 75 on my master file. These 75 rows would be more than I would ever ship in a day (unfortunately), yet would be a constant row reference number.
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 Posts

    Re: Print Loop (2007)

    Does this do what you want?

    <pre>Option Explicit
    Sub PrintLoop()
    Dim lRowStart As Long
    Dim lRowEnd As Long
    Dim iCol As Integer
    Dim lRef As Long
    Dim lNumRef As Long
    Dim rPrint As Range
    Dim rRef As Range
    'Set as desired
    Set rPrint = Range("A1:F25")
    Set rRef = Range("B3")
    iCol = 10 'Col J
    lRowStart = 8

    lRowEnd = Cells(Cells.Rows.Count, iCol).End(xlUp).Row
    lNumRef = lRowEnd - lRowStart + 1

    For lRef = 1 To lNumRef
    rRef.Value = lRef

    Set rRef = Nothing
    Set rPrint = Nothing
    End Sub</pre>


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts