Query, Share and Manage Excel workbooks on LAN, Internet

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.

Screenshot // Creating a custom Report Manager by writing a short VBA program in Microsoft Excel