Generating Reports Automatically in Excel VBA

An automated report is a report created or updated automatically using VBA.

VBA can collect data, perform calculations, format the report, and display the final result.

This saves time when the same type of report needs to be created regularly.

Automatic reports are useful because they can:

• Save time.
• Reduce repetitive work.
• Perform calculations automatically.
• Apply consistent formatting.
• Process large amounts of data.
• Create reports with one click.

Suppose a worksheet named Students contains:

VBA can use this data to create a separate report.

Create a new worksheet named Report.

The VBA macro will place the generated report on this worksheet.

Worksheets.Add
ActiveSheet.Name = "Report"

However, it is better to check whether the worksheet already exists before creating it.

Worksheet variables make the VBA code easier to manage.

Dim wsData As Worksheet
Dim wsReport As Worksheet

Set wsData = ThisWorkbook.Worksheets("Students")
Set wsReport = ThisWorkbook.Worksheets("Report")

Now wsData represents the source data and wsReport represents the report.

Before processing the data, VBA needs to know how many records are available.

Dim lastRow As Long

lastRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row

This finds the last used row in column A.

The report can contain headings such as:

wsReport.Range("A1").Value = "Student Report"
wsReport.Range("A3").Value = "Student ID"
wsReport.Range("B3").Value = "Name"
wsReport.Range("C3").Value = "Course"
wsReport.Range("D3").Value = "Marks"

These headings identify the information in the report.

A report should have a clear title.

wsReport.Range("A1").Value = "Student Performance Report"

The title can later be formatted using VBA.

We can make the report title bold and increase its font size.

With wsReport.Range("A1")
    .Font.Bold = True
    .Font.Size = 18
End With

The With statement makes it easier to apply multiple properties to the same cell.

A For loop can process every student record.

Dim i As Long

For i = 2 To lastRow

Next i

The loop starts at row 2 because row 1 contains the headings.

We need another variable to determine where each record should be written in the report.

Dim reportRow As Long

reportRow = 4

The report starts writing student records from row 4.

Student ID is stored in column A of the source worksheet.

wsReport.Cells(reportRow, 1).Value = _
    wsData.Cells(i, 1).Value

This copies the Student ID to the report.

Student Name can be copied from column B.

wsReport.Cells(reportRow, 2).Value = _
    wsData.Cells(i, 2).Value

The name is placed in column B of the report.

Course information can be copied from column C.

wsReport.Cells(reportRow, 3).Value = _
    wsData.Cells(i, 3).Value

The course is placed in column C of the report.

Marks can be copied from column D.

wsReport.Cells(reportRow, 4).Value = _
    wsData.Cells(i, 4).Value

The marks are placed in column D.

After writing one student record, increase the report row.

reportRow = reportRow + 1

This ensures that the next student is written on the next row.

A report can also show the total number of students.

wsReport.Range("F3").Value = "Total Students"
wsReport.Range("G3").Value = lastRow - 1

We subtract 1 because the first row contains headings.

If the report contains marks, VBA can calculate the total.

Dim totalMarks As Double

totalMarks = WorksheetFunction.Sum( _
    wsData.Range("D2:D" & lastRow))

The result contains the sum of all student marks.

VBA can also calculate the average marks.

Dim averageMarks As Double

averageMarks = WorksheetFunction.Average( _
    wsData.Range("D2:D" & lastRow))

This can be displayed in the report summary.

Report headings should be easy to identify.

With wsReport.Range("A3:D3")
    .Font.Bold = True
End With

This makes all four headings bold.

Borders can make the report easier to read.

With wsReport.Range("A3:D" & reportRow - 1)
    .Borders.LineStyle = xlContinuous
End With

This applies borders around the report data.

AutoFit can automatically adjust column widths.

wsReport.Columns("A:D").AutoFit

This makes the report more readable without manually adjusting each column.

If the report already contains old information, clear it before generating a new report.

wsReport.Cells.Clear

This removes existing values and formatting from the worksheet.

Use this carefully because it clears the entire worksheet.

A report can contain summary information such as:

• Total Students
• Total Marks
• Average Marks
• Highest Marks
• Lowest Marks

wsReport.Range("F3").Value = "Total Students"
wsReport.Range("F4").Value = "Average Marks"

VBA can use worksheet functions to find the highest and lowest marks.

Dim highestMarks As Double
Dim lowestMarks As Double

highestMarks = WorksheetFunction.Max( _
    wsData.Range("D2:D" & lastRow))

lowestMarks = WorksheetFunction.Min( _
    wsData.Range("D2:D" & lastRow))

These values can be included in the report summary.

The following macro copies student records and creates a basic formatted report.

Sub GenerateStudentReport()

    Dim wsData As Worksheet
    Dim wsReport As Worksheet
    Dim lastRow As Long
    Dim reportRow As Long
    Dim i As Long

    Set wsData = ThisWorkbook.Worksheets("Students")
    Set wsReport = ThisWorkbook.Worksheets("Report")

    wsReport.Cells.Clear

    lastRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row

    wsReport.Range("A1").Value = "Student Performance Report"

    With wsReport.Range("A1")
        .Font.Bold = True
        .Font.Size = 18
    End With

    wsReport.Range("A3").Value = "Student ID"
    wsReport.Range("B3").Value = "Name"
    wsReport.Range("C3").Value = "Course"
    wsReport.Range("D3").Value = "Marks"

    reportRow = 4

    For i = 2 To lastRow

        wsReport.Cells(reportRow, 1).Value = _
            wsData.Cells(i, 1).Value

        wsReport.Cells(reportRow, 2).Value = _
            wsData.Cells(i, 2).Value

        wsReport.Cells(reportRow, 3).Value = _
            wsData.Cells(i, 3).Value

        wsReport.Cells(reportRow, 4).Value = _
            wsData.Cells(i, 4).Value

        reportRow = reportRow + 1

    Next i

    With wsReport.Range("A3:D3")
        .Font.Bold = True
    End With

    With wsReport.Range("A3:D" & reportRow - 1)
        .Borders.LineStyle = xlContinuous
    End With

    wsReport.Columns("A:D").AutoFit

    MsgBox "Student report generated successfully."

End Sub

The current date can be displayed in the report.

wsReport.Range("A2").Value = "Report Date:"
wsReport.Range("B2").Value = Date

This allows users to know when the report was generated.

Excel VBA reports can be used for many purposes:

• Student reports
• Attendance reports
• Fee collection reports
• Sales reports
• Employee reports
• Inventory reports
• Monthly performance reports

Common mistakes while generating reports include:

• Using the wrong worksheet name.
• Using incorrect source columns.
• Forgetting to find the last row.
• Writing records to the wrong report row.
• Forgetting to increase the report row.
• Not formatting the generated report.
• Accidentally clearing important worksheet data.

A professional VBA report can combine data processing, calculations, formatting, and summary information.

A complete reporting system can include:

• Source data worksheet
• Report worksheet
• Automatic calculations
• Summary section
• Formatted headings
• Borders
• AutoFit columns
• Report date
• Search and filter options
• Print-ready layout

By combining these features, Excel VBA can turn a simple worksheet into a useful automated reporting application.