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

In this article, we’ll demonstrate a quick, effective method to generate reports in Excel using Macros.

Quick View of the Macro 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 a Report Using Macros in Excel: 2 Easy Steps

Suppose we have a large worksheet that contains the information about several sales representatives in two different datasets. We will generate a report using these two datasets.

Here’s an overview of dataset 1:

And here’s an overview of dataset 2:

Step 1 – Open Visual Basic Window

We’ll generate a report by using a simple VBA code, which is written in a VBA Module.

Steps:

To open a Module, go to Developer → Visual Basic.

A window named Microsoft Visual Basic for Applications – Generate Report opens.

Go to Insert → Module.

A Module window opens.

Step 2 – Write and Run VBA Macro Code to Generate Reports

Steps:

In the Module window, enter the following 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