You can create a print manager by using a macro (this works for Excel 97, 2000 and 2002).
Column A – This column contains numbers between 1 and 3: print from sheet, print by range name, or print from custom view (recommended).
Column B – Type the name of the sheet, range name (be sure to type the exact name of the range including the underscore. To make this simpler, paste the list of names by creating a shortcut with F3 and copying the name) and name of custom view.
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 into the footer, including page number, workbook name, path, sheet name, date and time of printing.
Sub PrintReports()
Dim NumberPages, PageNumber, i As Integer
Dim ActiveSh As Worksheet
Dim ChooseShNameView As String
Dim ShNameView As String
Application.ScreenUpdating = False
Set ActiveSh = ActiveSheet
For Each Cell In Range(Range("a2"), Range("a2").End(xlDown))
ShNameView = ActiveCell.Offset(0, 1).Value
PageNumber = ActiveCell.Offset(0, 2).Value
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
ActiveSh.Select
Application.ScreenUpdating = True
End Sub
Explanation and Comments:
1. The loop in the macro causes a separate print for each cell in column A starting at A2.
2. In the loop, the print area is selected using the Select Case technique.
4. The macro provided here only prints pages in the current workbook. You can add the option to print from other workbooks, even closed workbooks. Add two new columns, one for path and one for file name.
5. To run the macro, add a button to the sheet and attach the macro to it.
6. You can use this technique to add an unlimited number of reports.
Screenshot // Creating a custom Report Manager by writing a short VBA program in Microsoft Excel