How to Generate Reports Using Macros in Excel (with Easy Steps)

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 also. 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

generate reports in excel using macros


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


2 Easy Steps to Generate Reports Using Macros in Excel

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.

generate reports in excel using macros

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 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, firstly, from your Developer tab, go to,

Developer → Visual Basic

generate reports in excel using macros

  • 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

generate reports in excel using macros

Read More: How to Create a Summary Report in Excel (2 Easy Methods)


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

generate reports in excel using macros

  • 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 macros which has been given in the below screenshot.

generate reports in excel using macros


Things to Remember

👉 You can pop up 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


Conclusion

I hope all of the suitable methods mentioned above to generate reports with VBA code will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.


Related Articles

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo