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

Get FREE Advanced Excel Exercises with Solutions!

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

generate reports in excel using macros


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.

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

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

 


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 the macros that have been given in the below screenshot.

generate reports in excel using macros

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.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdur Rahim Rasel
Md. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo