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.

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


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.