PromptBase
🤖 GPT
MS Excel Macro for Automating Monthly Sales Report Generation
This prompt automates the tedious process of generating monthly sales reports, ensuring that all key metrics and performance indicators are calculated and presented in a visually appealing format with minimal user input.
...more
$3.99
After purchasing, you will gain access to the prompt file which you can use with GPT or the app builder. You'll receive 20 free generation credits with this purchase. By purchasing this prompt, you agree to our terms of service.
1 week ago
Related item
Prompt Details
Model
Chat - GPT-4o (gpt-4o)
Token size
164 ($0.00820 / call)
Example input
[Data Range]: A1
[Month]: August
[Year]: 2024
[Date Column Name]: Order Date
[Sales Column]: Sales Amount
[Transaction Column]: Order ID
[Salesperson]: Sales Rep
[Region]: Region
Example output
Below is a VBA (Excel Macro) code that automates the generation of a monthly sales report for "August 2024" based on the data starting from cell "A1". The macro performs the required steps, including filtering, summarizing, and generating a report with a chart.
### Steps:
1. Filter sales data by "Order Date".
2. Sum total sales and transactions.
3. Group data by "Sales Rep" and "Region".
4. Generate a formatted report.
5. Create a chart visualizing sales performance by salesperson.
### VBA Code:
```vba
Sub GenerateSalesReport()
' Prompt the user to select the worksheet with the sales data
Dim wsData As Worksheet
Dim wsReport As Worksheet
Dim wsName As String
On Error Resume Next
wsName = InputBox("Please enter the name of the worksheet with the sales data:")
Set wsData = Worksheets(wsName)
On Error GoTo 0
If wsData Is Nothing Then
MsgBox "Invalid worksheet name. Please try again."
Exit Sub
End If
' Clear old report if it exists
On Error Resume Next
Set wsReport = Worksheets("Monthly Sales Report")
If Not wsReport Is Nothing Then
Application.DisplayAlerts = False
wsReport.Delete
Application.DisplayAlerts = True
End If
On Error GoTo 0
' Add new worksheet for the report
Set wsReport = Worksheets.Add
wsReport.Name = "Monthly Sales Report"
' Filter data for August 2024
Dim lastRow As Long
lastRow = wsData.Cells(wsData.Rows.Count, 1).End(xlUp).Row ' Find last row with data
wsData.Range("A1").AutoFilter Field:=2, Criteria1:=">=08/01/2024", Operator:=xlAnd, Criteria2:="<=08/31/2024"
' Summarize total sales and transactions
Dim totalSales As Double
Dim totalTransactions As Long
totalSales = WorksheetFunction.SumIf(wsData.Range("B2:B" & lastRow), ">0", wsData.Range("C2:C" & lastRow)) ' Sum sales for August
totalTransactions = WorksheetFunction.CountIf(wsData.Range("B2:B" & lastRow), ">0") ' Count transactions for August
' Group sales data by Sales Rep and Region
wsData.Range("A1:D" & lastRow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wsReport.Range("A1"), Unique:=False
wsReport.Range("A1").CurrentRegion.RemoveDuplicates Columns:=Array(3, 4), Header:=xlYes
' Add summary headers
wsReport.Range("F1").Value = "Summary for August 2024"
wsReport.Range("F2").Value = "Total Sales:"
wsReport.Range("F3").Value = "Total Transactions:"
wsReport.Range("G2").Value = totalSales
wsReport.Range("G3").Value = totalTransactions
' Group data by Sales Rep and Region
wsReport.Range("A1").CurrentRegion.Sort Key1:=wsReport.Range("B1"), Order1:=xlAscending, Key2:=wsReport.Range("C1"), Order2:=xlAscending, Header:=xlYes
wsReport.Range("A1").CurrentRegion.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(3), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
' Generate Top 3 Sales Reps
wsReport.Range("F5").Value = "Top 3 Sales Reps:"
wsReport.Range("F6:G8").ClearContents
wsReport.Range("B1:C" & lastRow).Sort Key1:=wsReport.Range("C1"), Order1:=xlDescending, Header:=xlYes ' Sort sales by amount
wsReport.Range("F6").Value = "Sales Rep"
wsReport.Range("G6").Value = "Total Sales"
wsReport.Range("F7").Resize(3, 1).Value = wsReport.Range("B2:B4").Value
wsReport.Range("G7").Resize(3, 1).Value = wsReport.Range("C2:C4").Value
' Create chart for sales performance by salesperson
Dim chartObj As ChartObject
Set chartObj = wsReport.ChartObjects.Add(Left:=200, Width:=400, Top:=50, Height:=250)
With chartObj.Chart
.SetSourceData Source:=wsReport.Range("B1:C" & lastRow)
.ChartType = xlColumnClustered
.HasTitle = True
.ChartTitle.Text = "Sales Performance by Sales Rep"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Text = "Sales Rep"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Text = "Total Sales"
End With
' Autofit columns and set basic formatting
wsReport.Columns.AutoFit
wsReport.Range("F1:G3").Font.Bold = True
wsReport.Range("F5").Font.Bold = True
wsReport.Range("F7:G8").Interior.Color = RGB(255, 255, 204) ' Light yellow for top 3 sales
' Unfilter data in the data worksheet
wsData.AutoFilterMode = False
MsgBox "Monthly Sales Report for August 2024 generated successfully!", vbInformation
End Sub
```
### How the Macro Works:
1. **User Input**: The user is prompted to enter the name of the worksheet containing the sales data.
2. **Filtering**: It filters the sales data to include only transactions from August 2024 based on the "Order Date" column.
3. **Summing & Counting**: It calculates the total sales and total number of transactions.
4. **Group by Sales Rep & Region**: The data is grouped to show sales totals by "Sales Rep" and "Region."
5. **Top 3 Salespeople**: The macro identifies the top 3 salespeople based on total sales.
6. **Chart**: A clustered column chart is created to visualize sales performance by sales rep.
7. **Formatted Report**: The report is generated in a new worksheet, "Monthly Sales Report," with proper formatting.
### Assumptions:
- The sales data contains the following columns:
- **A1**: "Order ID"
- **B1**: "Order Date"
- **C1**: "Sales Amount"
- **D1**: "Sales Rep"
- **E1**: "Region"
Make sure the data is structured similarly. You can customize this macro based on your specific data setup.