If your application needs to print dynamic reports, then ideally to save tedious manual print setup, you need a re-usable 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 and Lotus 123, well there were 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 – in particular note this snippet from the sub:
Set WS = Sheet2
Set rng = Sheet2.Range(“P:W”)
iStartRow = 3
sKeyCol = “R”
sTitleRows = “$3:$5”
Call PrintAnyReport(WS, rng, sKeyCol, iStartRow, sTitleRows)
[vb]
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
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
[/vb]