x

How to Make a Report from Excel Sheets With VBA

by Suqing Wang
Jochen Sand/Digital Vision/Getty Images

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.

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.

Items you will need

  • Excel VBA

More Articles