You are here KNOWLEDGE Excel Tips Excel General Creating a custom Report Manager by writing a short VBA program in Microsoft Excel

Query, Share and Manage Excel workbooks on LAN, Internet

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

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
Creating a custom Report Manager by writing a short VBA program in Microsoft Excel