5 Advanced Excel Macros That Will Transform Your Business Operations

For businesses across Mayo, Galway, and Sligo, these five practical Excel macros will save you time and improve accuracy in your daily operations.

1. Automated Invoice Generator

Sub CreateInvoice()
    ' Set up variables
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim invNum As String
    
    ' Create new worksheet for invoice
    Set ws = Sheets.Add
    ws.Name = "Invoice_" & Format(Date, "dd-mm-yyyy")
    
    ' Set up invoice header
    ws.Range("A1").Value = "Your Company Name"
    ws.Range("A2").Value = "Address: Main Street, Castlebar, Co. Mayo"
    ws.Range("A3").Value = "Tel: 094 XXX XXXX | Email: info@yourcompany.ie"
    
    ' Create invoice number (YYYYMMDD-XX format)
    invNum = Format(Date, "yyyymmdd") & "-" & WorksheetFunction.CountA(Worksheets("Invoices").Range("A:A"))
    ws.Range("E2").Value = "Invoice #: " & invNum
    ws.Range("E3").Value = "Date: " & Format(Date, "dd/mm/yyyy")
    
    ' Copy customer and item data from template
    Sheets("Customer_Data").Range("A1:F10").Copy ws.Range("A6")
    
    ' Add VAT calculation (23% standard Irish rate)
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    ws.Range("E" & lastRow + 2).Value = "Subtotal:"
    ws.Range("F" & lastRow + 2).Formula = "=SUM(F6:F" & lastRow & ")"
    ws.Range("E" & lastRow + 3).Value = "VAT (23%):"
    ws.Range("F" & lastRow + 3).Formula = "=F" & lastRow + 2 & "*0.23"
    ws.Range("E" & lastRow + 4).Value = "Total:"
    ws.Range("F" & lastRow + 4).Formula = "=F" & lastRow + 2 & "+F" & lastRow + 3
    
    ' Format and save
    ws.Range("A1:F" & lastRow + 4).Columns.AutoFit
End Sub

How to use it: Create a "Customer_Data" sheet with your client details and products/services. Run this macro to generate a professional invoice with Irish VAT rates automatically calculated.

2. Weekly Sales Report for Multiple Locations

Sub CreateWeeklySalesReport()
    ' Create report for multiple shop locations
    Dim wbReport As Workbook
    Dim wsData As Worksheet
    Dim wsReport As Worksheet
    Dim lastRow As Long, i As Integer
    Dim locations As Variant
    
    ' Define West of Ireland locations
    locations = Array("Galway City", "Westport", "Sligo Town", "Castlebar")
    
    ' Create new workbook for report
    Set wbReport = Workbooks.Add
    Set wsReport = wbReport.Sheets(1)
    wsReport.Name = "Weekly_Sales_Report"
    
    ' Set up report header
    wsReport.Range("A1").Value = "Weekly Sales Report: " & Format(Date - 7, "dd/mm/yyyy") & " to " & Format(Date, "dd/mm/yyyy")
    wsReport.Range("A3:F3") = Array("Location", "Total Sales", "Transaction Count", "Avg Transaction", "YoY Change", "Notes")
    
    ' Format header
    wsReport.Range("A3:F3").Font.Bold = True
    
    ' Get data for each location
    For i = 0 To UBound(locations)
        wsReport.Range("A" & i + 4).Value = locations(i)
        
        ' Extract sales data from location-specific sheets
        On Error Resume Next
        Set wsData = Worksheets(locations(i))
        
        If Not wsData Is Nothing Then
            ' Calculate total sales
            wsReport.Range("B" & i + 4).Value = WorksheetFunction.Sum(wsData.Range("F:F"))
            
            ' Count transactions
            wsReport.Range("C" & i + 4).Value = WorksheetFunction.CountA(wsData.Range("A:A")) - 1
            
            ' Calculate average transaction
            wsReport.Range("D" & i + 4).Formula = "=B" & i + 4 & "/C" & i + 4
            
            ' YoY comparison (placeholder - would connect to historical data)
            wsReport.Range("E" & i + 4).Value = "=IFERROR((B" & i + 4 & "-LastYearSheet!B" & i + 4 & ")/LastYearSheet!B" & i + 4 & ",""N/A"")"
        End If
        On Error GoTo 0
    Next i
    
    ' Format currency for Euro
    wsReport.Range("B4:D" & 4 + UBound(locations)).NumberFormat = "€#,##0.00"
    wsReport.Range("E4:E" & 4 + UBound(locations)).NumberFormat = "0.0%"
    
    ' Create chart
    Dim chtObj As ChartObject
    Set chtObj = wsReport.ChartObjects.Add(Left:=300, Width:=450, Top:=50, Height:=250)
    
    With chtObj.Chart
        .SetSourceData Source:=wsReport.Range("A4:B" & 4 + UBound(locations))
        .ChartType = xlColumnClustered
        .HasTitle = True
        .ChartTitle.Text = "Sales by Location"
    End With
End Sub

How to use it: Create sheets named after each location with sales data in column F. Run this macro to generate a consolidated report with totals, averages, and a chart comparing sales across your West of Ireland branches.

3. Tourism Season Pricing Updater

Sub UpdateSeasonalPricing()
    ' Updates product/room prices based on season
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim season As String
    Dim i As Long
    
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Determine current season for West of Ireland tourism
    Select Case Month(Date)
        Case 6, 7, 8
            season = "High" ' Summer peak
        Case 5, 9
            season = "Mid" ' Shoulder season
        Case Else
            season = "Low" ' Off-peak
    End Select
    
    ' Apply seasonal multipliers to base prices
    For i = 2 To lastRow ' Assuming header in row 1
        ' Get base price from column B
        basePrice = ws.Cells(i, 2).Value
        
        ' Apply seasonal multiplier
        Select Case season
            Case "High"
                ws.Cells(i, 3).Value = basePrice * 1.4 ' 40% premium in peak season
            Case "Mid"
                ws.Cells(i, 3).Value = basePrice * 1.2 ' 20% premium in shoulder season
            Case "Low"
                ws.Cells(i, 3).Value = basePrice * 0.9 ' 10% discount in off-peak
        End Select
    Next i
    
    ' Format and add information
    ws.Range("E1").Value = "Pricing Updated for " & season & " Season"
    ws.Range("E2").Value = "Date: " & Format(Date, "dd/mm/yyyy")
    ws.Range("A1:E" & lastRow).Columns.AutoFit
    
    MsgBox "Seasonal pricing updated for " & season & " season.", vbInformation
End Sub

How to use it: Create a sheet with product/room names in column A and base prices in column B. This macro automatically calculates seasonal rates based on West of Ireland tourism patterns and updates column C with the current seasonal price.

4. Supplier Distance Calculator for Delivery Routing

Sub CalculateSupplierDistances()
    ' Calculate distances from your business to suppliers
    Dim ws As Worksheet, wsResults As Worksheet
    Dim lastRow As Long, i As Long
    Dim yourLocation As String
    Dim lat1 As Double, lon1 As Double
    Dim lat2 As Double, lon2 As Double
    Dim distance As Double
    
    ' Set your business location (example: Galway City)
    yourLocation = "Galway City"
    lat1 = 53.2707 ' Galway coordinates
    lon1 = -9.0568
    
    ' Create results sheet
    On Error Resume Next
    Set wsResults = Sheets("Delivery_Routes")
    If wsResults Is Nothing Then
        Set wsResults = Sheets.Add
        wsResults.Name = "Delivery_Routes"
    End If
    On Error GoTo 0
    
    ' Setup headers
    wsResults.Range("A1:E1") = Array("Supplier", "Location", "Distance (km)", "Estimated Time", "Route Priority")
    wsResults.Range("A1:E1").Font.Bold = True
    
    ' Get supplier data
    Set ws = Sheets("Suppliers")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Process each supplier
    For i = 2 To lastRow
        ' Get supplier details
        supplierName = ws.Cells(i, 1).Value
        supplierLocation = ws.Cells(i, 2).Value
        
        ' Get coordinates (would normally use API, using sample data here)
        Select Case supplierLocation
            Case "Sligo"
                lat2 = 54.2697
                lon2 = -8.4694
            Case "Castlebar"
                lat2 = 53.8552
                lon2 = -9.2982
            Case "Westport"
                lat2 = 53.8004
                lon2 = -9.5144
            Case "Clifden"
                lat2 = 53.4889
                lon2 = -10.0201
            Case Else
                lat2 = 53.5 ' Default
                lon2 = -9.0
        End Select
        
        ' Calculate distance using Haversine formula
        distance = HaversineDistance(lat1, lon1, lat2, lon2)
        
        ' Write to results sheet
        wsResults.Cells(i, 1).Value = supplierName
        wsResults.Cells(i, 2).Value = supplierLocation
        wsResults.Cells(i, 3).Value = Round(distance, 1)
        
        ' Estimate travel time (60 km/h average speed on regional roads)
        wsResults.Cells(i, 4).Value = Round(distance / 60 * 60, 0) & " mins"
        
        ' Set priority based on distance
        If distance < 30 Then
            wsResults.Cells(i, 5).Value = "High"
        ElseIf distance < 75 Then
            wsResults.Cells(i, 5).Value = "Medium"
        Else
            wsResults.Cells(i, 5).Value = "Low"
        End If
    Next i
    
    ' Format and sort
    wsResults.Range("A1:E" & lastRow).Columns.AutoFit
    wsResults.Range("A2:E" & lastRow).Sort Key1:=wsResults.Range("C2"), Order1:=xlAscending
    
    MsgBox "Delivery routes calculated from " & yourLocation & " to " & lastRow - 1 & " suppliers.", vbInformation
End Sub

Function HaversineDistance(lat1 As Double, lon1 As Double, lat2 As Double, lon2 As Double) As Double
    ' Calculate distance between two points on Earth
    Const PI As Double = 3.14159265358979
    Const EARTH_RADIUS As Double = 6371 ' km
    
    Dim dLat As Double, dLon As Double
    Dim a As Double, c As Double, d As Double
    
    ' Convert to radians
    lat1 = lat1 * PI / 180
    lon1 = lon1 * PI / 180
    lat2 = lat2 * PI / 180
    lon2 = lon2 * PI / 180
    
    ' Calculate differences
    dLat = lat2 - lat1
    dLon = lon2 - lon1
    
    ' Calculate distance
    a = Sin(dLat / 2) ^ 2 + Cos(lat1) * Cos(lat2) * Sin(dLon / 2) ^ 2
    c = 2 * Atn2(Sqr(a), Sqr(1 - a))
    d = EARTH_RADIUS * c
    
    HaversineDistance = d
End Function

How to use it: Create a "Suppliers" sheet with supplier names in column A and locations in column B. This macro calculates the distance between your business and suppliers across Mayo, Galway and Sligo, helping optimize delivery routes and schedules.

5. Grant Application Budget Generator

Sub CreateGrantBudget()
    ' Creates a formatted budget for Enterprise Ireland/LEADER applications
    Dim ws As Worksheet
    Dim lastRow As Long
    
    ' Create new sheet
    Set ws = Sheets.Add
    ws.Name = "Grant_Budget"
    
    ' Set up header information
    ws.Range("A1").Value = "Business Expansion Grant Budget"
    ws.Range("A2").Value = "For submission to: Enterprise Ireland / LEADER"
    ws.Range("A3").Value = "Business Name: Your Business Name"
    ws.Range("A4").Value = "County: Mayo/Galway/Sligo"
    ws.Range("A5").Value = "Date Prepared: " & Format(Date, "dd/mm/yyyy")
    
    ' Format header
    ws.Range("A1").Font.Bold = True
    ws.Range("A1").Font.Size = 14
    
    ' Create category headers
    ws.Range("A7:F7") = Array("Category", "Description", "Quantity", "Unit Cost (€)", "Total Cost (€)", "Eligible for Funding")
    ws.Range("A7:F7").Font.Bold = True
    ws.Range("A7:F7").Interior.Color = RGB(200, 200, 200)
    
    ' Sample budget items - would normally be pulled from data sheet
    budgetItems = Array( _
        Array("Equipment", "Manufacturing Equipment", 1, 25000, "=C8*D8", "Yes"), _
        Array("Equipment", "Computer Hardware", 5, 1200, "=C9*D9", "Yes"), _
        Array("Equipment", "Office Furniture", 10, 300, "=C10*D10", "No"), _
        Array("Staff", "Production Staff", 2, 35000, "=C11*D11", "Yes"), _
        Array("Staff", "Admin Support", 1, 32000, "=C12*D12", "No"), _
        Array("Marketing", "Website Development", 1, 5000, "=C13*D13", "Yes"), _
        Array("Marketing", "Trade Show (Dublin)", 1, 3500, "=C14*D14", "Yes"), _
        Array("Premises", "Renovations", 1, 15000, "=C15*D15", "Yes"), _
        Array("Training", "Staff Upskilling", 3, 1200, "=C16*D16", "Yes"), _
        Array("Other", "Certification Costs", 1, 2800, "=C17*D17", "Yes") _
    )
    
    ' Add budget items to sheet
    For i = 0 To UBound(budgetItems)
        For j = 0 To UBound(budgetItems(i))
            ws.Cells(i + 8, j + 1).Value = budgetItems(i)(j)
        Next j
    Next i
    
    lastRow = 8 + UBound(budgetItems)
    
    ' Add totals
    ws.Range("D" & lastRow + 2).Value = "Total Project Cost:"
    ws.Range("E" & lastRow + 2).Formula = "=SUM(E8:E" & lastRow & ")"
    
    ws.Range("D" & lastRow + 3).Value = "Total Eligible Cost:"
    ws.Range("E" & lastRow + 3).Formula = "=SUMIF(F8:F" & lastRow & ",""Yes"",E8:E" & lastRow & ")"
    
    ws.Range("D" & lastRow + 4).Value = "Grant Requested (50%):"
    ws.Range("E" & lastRow + 4).Formula = "=E" & lastRow + 3 & "*0.5"
    
    ' Format currency
    ws.Range("D8:E" & lastRow + 4).NumberFormat = "€#,##0.00"
    
    ' Add notes about Western region priority
    ws.Range("A" & lastRow + 6).Value = "Note: Projects in Mayo, Galway, and Sligo may qualify for additional regional aid under the Western Regional Development Fund."
    
    ' Add border, format and autofit
    ws.Range("A7:F" & lastRow).Borders.LineStyle = xlContinuous
    ws.Range("A1:F" & lastRow + 6).Columns.AutoFit
    
    MsgBox "Grant budget created with regional funding notes.", vbInformation
End Sub

How to use it: Run this macro to generate a professional budget sheet formatted to Enterprise Ireland and LEADER funding requirements, with regional development fund notes specific to Western Ireland counties.

These macros can be implemented in your business right away. Simply open the Visual Basic Editor in Excel (Alt+F11), insert a new module, and paste the code.