How to Make a Report from Excel Sheets With VBA
By Suqing Wang
Using Visual Basic for Application (VBA), you can develop VBA procedures in the Excel Visual Basic Editor (VBE). It is an easy-to-use development environment. VBA procedures developed in the VBE will be a part of the workbook. You can develop a VBA application to automatically generate reports in Excel. You need to enable the Macro before you can program with VBE.
Step 1
Click "Start," "All Programs" and "Microsoft Excel" to enter the Excel interface. Click the "Developer" ribbon and then "Macro Security" button. Check "Disable all Macros with Notification" and click "Save." Now you have set up Macros in your Excel.
Step 2
Press "Alt" and "F11" concurrently to launch VBE. Double-click "Sheet1" on your right pane in your VBE to open the code window. Select "Worksheet" on the drop-down options in the code window.
Step 3
Enter the following code in the code window:
Sub PrintReport()
Dim Page_number
Dim ActiveSh As Worksheet
Dim ShNameView As String
Set ActiveSh=ActiveSheet
For Each Cell in Range (Range ("b3"), Range ("b3").End (xlDown))
Sh_view=ActiveCell.Offset (0, 1).Value
Page_number= ActiveCell.Offset (0, 1).Value
Select Case Cell.Value
Case 1
Sheets (ShNameView).Select
Case 2
Application. Goto Reference:=ShNameView
End Select
ActiveWindow.SeletedSheets.Printout Copies:=1
Next
The loop in the code leads to a print for each cell in column B starting from B2. The code only prints pages in the current workbook.
References
Writer Bio
Suqing Wang started writing professionally in 2008. Her writing expertise is in education and information technology. Wang's articles have appeared in "The Engineering of Computers" and in various online publications. She has spent many years lecturing on information technology. Wang earned a Master of Engineering in computing from Shandong University in China.