Re-usable Print Macro

If you like this post please share it!
  •  
  •  
  •  
  •  
  •  
  • 3
  •  
  •  

print
If your application needs to print dynamic reports, then ideally to save tedious manual print setup, you need a print macro. If your application needs to print several dynamic reports it’s even better to have one versatile print routine that is passed the parameters it needs to do the job. One print macro is easier to maintain than several and tedious settings like orientation, margins or footers etc. is set once. One of the goals of good coding practice is to re-use routines. So below is an example of a routine I wrote for an application I did for a client that used their SAP generated stock data and created a Pick List that picked stock in their warehouse in an optimal way taking account of expiry dates i.e. FIFO if the stock had one. It also created a Back Orders List for stock that was insufficient to fulfill the order plus one other list.  By the way I use the word macro interchangeably with VBA subs – I know some people don’t like the word macro as it might imply that this is the recorded version of VBA but anyway call me ‘old school’ when I first started with spreadsheets well there was just macros.

So the parameters tell the PrintAnyReport VBA procedure which worksheet the range is on, that is the range, the column in which to look for the last cell, the start row of the range and the title rows to use.   Re the last cell part – I am taking the practical view here that there is an obvious common sense column that will reliably dictate the last row of the report.  In this case it was was the numeric material code – I knew from how I put together this application that it was impossible to not have a material code listed so finding the last cell of this column was reliably the last cell of this report.  PrintAnyReport uses lastrow = WS.Cells(Cells.Rows.Count, KeyCol).End(xlUp).Row to then find this.

If you look further below there is an example of the sub to print a Pick List that calls my PrintAnyReport sub but before doing this sets or defines  each of the parameters it needs to for this specific report and pass to PrintAnyReport via the Call statement to make it all happen – see this snippet:

Set WS = Sheet2
Set rng = Sheet2.Range(“P:W”)
iStartRow = 3
sKeyCol = “R”
sTitleRows = “$3:$5”
Call PrintAnyReport(WS, rng, sKeyCol, iStartRow, sTitleRows)

Sub PrintAnyReport(WS As Worksheet, PRng As Range, KeyCol As String, StartRow As Integer, TitleRows As String)
'---------------------------------------------------------------------------------------
' Module    : PrintAnyReport
' Author    : John Hackwood theexcelfactor.com e:[email protected]
' Date      : 29/09/15
' Purpose   : Module is for any dynamic report where KeyCol is the column which will be searched
'             for last row
'---------------------------------------------------------------------------------------

    Dim iLastrow As Integer
    'Needs FindLastRow function to operate
    Dim r1 As Range
    Dim r2 As Range
    Dim sR3 As String
    Dim rPrtRng As Range
    Dim lastrow As Long

    Set r1 = PRng
    Call SpeedUp
    WS.Activate

    On Error Resume Next
    lastrow = WS.Cells(Cells.Rows.Count, KeyCol).End(xlUp).Row
    On Error GoTo 0
    If lastrow < StartRow Then lastrow = StartRow + 1
    sR3 = StartRow & ":" & lastrow
    Set r2 = WS.Range(sR3)
    Set rPrtRng = Application.Intersect(r1, r2)
    
    
    With ActiveSheet.PageSetup
        .PrintArea = rPrtRng.Address
        .PrintTitleRows = TitleRows
        .PrintTitleColumns = ""
        .PrintHeadings = False
        .PrintGridlines = False
        .RightFooter = "&8&D at &T"
        .CenterFooter = "Page &P of &N"
        .LeftFooter = "&6&Z&F"
        .LeftMargin = Application.InchesToPoints(0.2)
        .RightMargin = Application.InchesToPoints(0.2)
        .TopMargin = Application.InchesToPoints(0.5)
        .BottomMargin = Application.InchesToPoints(0.4)
        .HeaderMargin = Application.InchesToPoints(0.1)
        .FooterMargin = Application.InchesToPoints(0.1)
        .Orientation = xlLandscape
        .FitToPagesWide = 1
        .FitToPagesTall = False
    End With
    Application.Dialogs(xlDialogPrinterSetup).Show
    'ActiveSheet.PrintPreview
    ActiveSheet.PrintOut


    Set rPrtRng = Nothing
    Set r1 = Nothing
    Set r2 = Nothing

    Call ResetSpeedup
End Sub

Sub PrintPickList()
'---------------------------------------------------------------------------------------
' Module    : PrintPickList
' Author    : John Hackwood theexcelfactor.com e:[email protected]
' Date      : 29/09/15
' Purpose   : Code for specifics of Pick List Report which is passed to sub
'               PrintAnyReport
'---------------------------------------------------------------------------------------
    Dim WS As Worksheet
    Dim rng As Range
    Dim iStartRow As Integer
    Dim sKeyCol As String
    Dim sTitleRows As String

    Set WS = Sheet2
    Set rng = Sheet2.Range("P:W")
    iStartRow = 3
    sKeyCol = "R"
    sTitleRows = "$3:$5"
    Call PrintAnyReport(WS, rng, sKeyCol, iStartRow, sTitleRows)

    Set WS = Nothing
    Set rng = Nothing

End Sub


Tagged with: , , , ,