Using the Report Manager to print reports is not a good solution for printing reports from single or multiple workbooks and does not provide an automatic solution for custom numbering of pages.
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.