» Creating a Custom Report Manager

You can create a custom Print Reports by adding VBA macro to a regular Module sheet (the macro can be downloaded from www.exceltip.com).

Structure of the Custom Print Report:

2. Column A: This column contains numbers between 1 and 3: print from sheet, print by range name, or print from Custom View (recommended).
3. Column B: Type the sheet name, range Name, or Custom View name.
4. Column C: Type the page number to be printed in the footer.

The macro will print from a sheet and automatically add the necessary information to the footer, including page number, workbook name, path address, and sheet name, as well as the date and time of printing.


Sub PrintReports()

Dim NumberPages As Integer, PageNumber As Integer, i As Integer
Dim ActiveSh As Worksheet, ChooseShNameView As String
Dim ShNameView As String, cell As Range

Application.ScreenUpdating = False
Set ActiveSh = ActiveSheet
Range("a2").Select

For Each cell In Range(Range("a2"), Range("a2").End(xlDown))


Select Case cell.Value
Case 1
Sheets(ShNameView).Select
Case 2
Application.GoTo Reference:=ShNameView
Case 3
ActiveWorkbook.CustomViews(ShNameView).Show
End Select

With ActiveSheet.PageSetup
. CenterFooter.PageNumber
.LeftFooter = ActiveWorkbook.FullName & " " & "&A &T &D"
End With

ActiveWindow.SelectedSheets.PrintOut Copies:=1


Next i

ActiveSh.Select
Application.ScreenUpdating = True

End Sub

5. The For Each loop in the macro causes a separate print for each cell in column A starting at A2.
6. In the loop, the print area is selected using the Select Case technique.
7. The information printed on the left side of the footer: &08 = 8 point font, &D = Date, &T = Time.
8. To run the macro from the sheet, press Alt+F8, select the macro and click Run.
OR
Add a button to the sheet and attach the macro to it.
9. Note: Use this technique to add an unlimited number of reports.
Screenshot // Creating a Custom Report Manager