Automate data analysis reports, Excel VBA

Here is an example of a VBA (Visual Basic for Applications) code that can be used to automate data analysis reports in Excel. This example takes data from a table, performs some simple calculations (such as totals and averages), and then generates a report in a new worksheet.

Scenario:

Assume you have a data table in an Excel worksheet with the following columns:

• Name (Column A)
• Sales (Column B)
• Cost (Column C)

The goal is to create a report in a new sheet that calculates the total sales, total cost, average sales, and average cost.

VBA Code:

Sub AutomateDataAnalysisReport()
    Dim wsData As Worksheet
    Dim wsReport As Worksheet
    Dim lastRow As Long
    Dim totalSales As Double
    Dim totalCost As Double
    Dim avgSales As Double
    Dim avgCost As Double
    Dim i As Long
    ' Set the data worksheet and check if it exists
    On Error Resume Next
    Set wsData = ThisWorkbook.Sheets("Data")
    On Error GoTo 0
    If wsData Is Nothing Then
        MsgBox "The 'Data' sheet does not exist.", vbExclamation
        Exit Sub
    End If
    ' Find the last row of data in column A
    lastRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row
    ' Create a new worksheet for the report
    Set wsReport = ThisWorkbook.Sheets.Add
    wsReport.Name = "AnalysisReport"
    ' Title of the report
    wsReport.Cells(1, 1).Value = "Data Analysis Report"
    wsReport.Cells(2, 1).Value = "Date: " & Date
    wsReport.Cells(3, 1).Value = "Name"
    wsReport.Cells(3, 2).Value = "Sales"
    wsReport.Cells(3, 3).Value = "Cost"
    ' Copy the data from the "Data" sheet to the "AnalysisReport" sheet
    wsData.Range("A1:C" & lastRow).Copy
    wsReport.Range("A4").PasteSpecial Paste:=xlPasteValues
    ' Initialize totals
    totalSales = 0
    totalCost = 0
    ' Calculate totals and averages
    For i = 4 To lastRow
        totalSales = totalSales + wsReport.Cells(i, 2).Value
        totalCost = totalCost + wsReport.Cells(i, 3).Value
    Next i
    ' Calculate averages
    avgSales = totalSales / (lastRow - 3)
    avgCost = totalCost / (lastRow - 3)
    ' Display the results in the report
    wsReport.Cells(lastRow + 2, 1).Value = "Total Sales:"
    wsReport.Cells(lastRow + 2, 2).Value = totalSales
    wsReport.Cells(lastRow + 3, 1).Value = "Total Cost:"
    wsReport.Cells(lastRow + 3, 2).Value = totalCost
    wsReport.Cells(lastRow + 4, 1).Value = "Average Sales:"
    wsReport.Cells(lastRow + 4, 2).Value = avgSales
    wsReport.Cells(lastRow + 5, 1).Value = "Average Cost:"
    wsReport.Cells(lastRow + 5, 2).Value = avgCost
    ' Format the report
    wsReport.Columns("A:C").AutoFit
    wsReport.Range("A3:C" & lastRow).Borders(xlEdgeBottom).LineStyle = xlContinuous
    wsReport.Range("A1:C1").Font.Bold = True
    wsReport.Range("A1:C1").HorizontalAlignment = xlCenter
    MsgBox "Report generated successfully!", vbInformation
End Sub

Explanation of the Code:

1. Set the Data and Report Worksheets:
   • wsData references the sheet containing the data (named « Data »).
   • wsReport is the sheet where the report will be generated.
2. Check if the Data Sheet Exists:
   • The code checks if the « Data » sheet exists. If it doesn’t, it shows an error message and stops the macro.
3. Find the Last Row of Data:
   • The variable lastRow identifies the last row containing data in column A of the « Data » sheet.
4. Create a New Report Worksheet:
   • A new sheet called « AnalysisReport » is created, and a title is added to the first cell.
5. Copy the Data:
   • The data from the « Data » sheet (from A1 to C and the last row) is copied to the « AnalysisReport » sheet.
6. Calculate Totals and Averages:
   • A loop is used to calculate the total sales and total cost.
   • The averages of sales and costs are calculated by dividing the totals by the number of rows.
7. Display the Results:
   • The results of the calculations (totals and averages) are displayed at the end of the report.
8. Format the Report:
   • The columns are auto-sized to fit the content.
   • Borders are added to the data rows.
   • The title of the report is made bold and centered.
9. Confirmation Message:
   • After the macro finishes running, a message box is displayed to confirm that the report has been generated successfully.

How to Use the Code:

1. Open Excel and press Alt + F11 to open the VBA editor.
2. In the editor, insert a new module by clicking Insert > Module.
3. Copy and paste the above code into the module.
4. Run the code by pressing F5 or by assigning the macro to a button in your Excel sheet.

This code provides a basic template for automating data analysis in Excel. You can modify it to perform more complex calculations or customize the report formatting to suit your needs.