|
|||||||||
|
|||||||||
|
||||||||||||||||||||||||||||
» 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: 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 | ||||||||||||||||
 | ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
|
READER COMMENTS (view all comments) |
||||||||||||||||
|
No comments have been submitted. | ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
 | ||||||||||||||||
Microsoft Outlook 2002 for Dummies
Microsoft Word Version 2002 Inside Out
Dictionary of Finance and Investment Terms
Retire Young, Retire Rich
Real Estate Loopholes: Secrets of Successful Real Estate Investing
The Guide to Understanding Financial Statements | ||||||||||||||||
| ||||||||||||||||
RELATED MICROSOFT EXCEL TIPS
 |
• Excel VBA books
• Accounting books
• Business Plans
• MS Office books
• Taxes books
|
Terms
and Conditions of use |
|
|
Copyright © 2003 ExcelTip.com |
|