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

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

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

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

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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

Advanced Excel Exercises with Solutions PDF