While working with Microsoft Excel, sometimes we need to generate a report. Generating a report in Excel is an easy task. This is a time-saving task. Today, in this article, we’ll learn two quick and suitable steps to generate reports in Excel using Macros effectively with appropriate illustrations.
Generate Reports Using Macros in Excel (Quick View)
Private Sub GR()
Dim nextrow As Long
Sheets("Generate Report").Select
Sheets("Generate Report").Cells.ClearContents
Range("A1").Value = "Name"
Range("B1").Value = "ID"
Range("C1").Value = "Product"
Range("D1").Value = "Rev earned"
For x = 1 To Sheets.Count - 1
Sheets(x).Range("A2:D50").Copy
nextrow = Sheets("Generate Report").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
Sheets("Generate Report").Cells(nextrow, 1).PasteSpecial Paste:=xlPasteValues
Next
Cells(1, 5).Select
Application.CutCopyMode = False
Sheets("Generate Report").Range(Cells(1, 1), Cells(nextrow, 4)).Columns.AutoFit
End Sub
How to Generate Reports Using Macros in Excel: 2 Easy Steps
Let’s assume we have an Excel large worksheet that contains the information about several sales representatives of Armani Group. The name of the sales representatives, their Identification Number, types of Products, and the Revenue Earned by the sales representatives are given in Columns B, C, D, and E respectively. We will generate reports using Macros in Excel. Let’s say, we have two different datasets. We will generate a report using these two datasets. Here’s an overview of the dataset 1 for today’s task.
Here’s an overview of the dataset 2 for today’s task.
Step 1: Enable Visual Basic Window
First of all, we will enable the VBA macros to generate a report. Now I’ll show you how to generate reports in Excel using macros by using a simple VBA code. It’s very helpful for some particular moments. From our dataset, we will generate reports using macros. Let’s follow the instructions below to generate a report!
- First of all, open a Module, To do that, first, from your Developer tab, go to
Developer → Visual Basic
- After clicking on the Visual Basic ribbon, a window named Microsoft Visual Basic for Applications – Generate Report will instantly appear in front of you.
- From the Microsoft Visual Basic for Applications – Generate Report window, we will insert a module for applying our VBA code. To do that, go to,
Insert → Module
Step 2: Run VBA Macro Code to Generate Reports in Excel
After creating a module, we will write down the below VBA code in the Generate Report module. Let’s follow the instructions below to run the VBA code!
- Write down the below VBA code,
Private Sub GR()
Dim nextrow As Long
Sheets("Generate Report").Select
Sheets("Generate Report").Cells.ClearContents
Range("A1").Value = "Name"
Range("B1").Value = "ID"
Range("C1").Value = "Product"
Range("D1").Value = "Rev earned"
For x = 1 To Sheets.Count - 1
Sheets(x).Range("A2:D50").Copy
nextrow = Sheets("Generate Report").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
Sheets("Generate Report").Cells(nextrow, 1).PasteSpecial Paste:=xlPasteValues
Next
Cells(1, 5).Select
Application.CutCopyMode = False
Sheets("Generate Report").Range(Cells(1, 1), Cells(nextrow, 4)).Columns.AutoFit
End Sub
- After that, we will run the VBA To do that, go to,
Run → Run Sub/UserForm
- As a result, we will be able to generate a report in Excel using the macros that have been given in the below screenshot.
Read More: How to Generate Report in PDF Format Using Excel VBA
Things to Remember
👉 You can pop up the Microsoft Visual Basic for Applications window by pressing Alt + F11 simultaneously.
👉 If a Developer tab is not visible in your ribbon, you can make it visible. To do that, go to,
File → Option → Customize Ribbon
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
I hope all of the suitable methods mentioned above to generate reports with VBA code will now provoke you to apply them to your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.
