Print Loop (2007)

Topic

New Reply

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.

Reply | Quote

Replies

Does this do what you want?

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
    rPrint.PrintOut 
  Next
    
  Set rRef = Nothing
  Set rPrint = Nothing
End Sub

Steve

Reply | Quote