12. Macros and VBA

12.12 Practical Macros on Our Data

By Ravindra Bagale · Last updated: 6 October 2026

A. Format the daily FreshGo report

Sub FormatDailyReport()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim rupeeFmt As String

    Set ws = ThisWorkbook.Worksheets("Orders")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    If lastRow < 2 Then
        MsgBox "No orders found.", vbExclamation
        Exit Sub
    End If

    ' Indian rupee format; ChrW(8377) is the rupee symbol
    rupeeFmt = "[>=10000000]" & ChrW(8377) & "##\,##\,##\,##0;" & _
               "[>=100000]" & ChrW(8377) & "##\,##\,##0;" & ChrW(8377) & "##,##0"

    Application.ScreenUpdating = False
    With ws.Range("A1:I1")
        .Font.Bold = True
        .Font.Color = RGB(255, 255, 255)
        .Interior.Color = RGB(33, 115, 70)
    End With
    ws.Range("B2:B" & lastRow).NumberFormat = "dd-mm-yyyy"
    ws.Range("G2:G" & lastRow).NumberFormat = rupeeFmt

    ' Highlight late deliveries (more than 15 minutes)
    With ws.Range("H2:H" & lastRow)
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=15"
        .FormatConditions(1).Interior.Color = RGB(255, 199, 206)
    End With

    ws.Columns("A:I").AutoFit
    ws.Activate
    ActiveWindow.FreezePanes = False
    ws.Range("A2").Select
    ActiveWindow.FreezePanes = True
    Application.ScreenUpdating = True
    MsgBox "Daily report formatted: " & lastRow - 1 & " orders.", vbInformation
End Sub

B. Trim spaces in a column

Sub TrimCityColumn()
    Dim ws As Worksheet
    Dim cell As Range
    Dim lastRow As Long

    Set ws = ThisWorkbook.Worksheets("Orders")
    lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row

    For Each cell In ws.Range("C2:C" & lastRow)
        If Not IsError(cell.Value) And Not cell.HasFormula Then
            If VarType(cell.Value) = vbString Then
                ' Replace non-breaking spaces, then Excel's TRIM (also removes double spaces inside)
                cell.Value = Application.WorksheetFunction.Trim(Replace(cell.Value, ChrW(160), " "))
            End If
        End If
    Next cell
End Sub

VBA's own Trim() removes only leading/trailing spaces; WorksheetFunction.Trim also reduces inside spaces to one, like the worksheet TRIM.

C. Split data into one sheet per city

Sub SplitByCity()
    Dim src As Worksheet, ws As Worksheet
    Dim cities As Object
    Dim lastRow As Long, lastCol As Long, r As Long
    Dim city As Variant
    Dim dataRng As Range

    Set src = ThisWorkbook.Worksheets("Orders")
    lastRow = src.Cells(src.Rows.Count, "A").End(xlUp).Row
    lastCol = src.Cells(1, src.Columns.Count).End(xlToLeft).Column
    Set dataRng = src.Range(src.Cells(1, 1), src.Cells(lastRow, lastCol))

    ' Collect distinct city names (column C) in a Dictionary
    Set cities = CreateObject("Scripting.Dictionary")
    For r = 2 To lastRow
        city = Trim(CStr(src.Cells(r, 3).Value))
        If Len(city) > 0 Then
            If Not cities.Exists(city) Then cities.Add city, 1
        End If
    Next r

    Application.ScreenUpdating = False
    If src.AutoFilterMode Then src.AutoFilterMode = False

    For Each city In cities.Keys
        ' Delete an old sheet of the same name, if any
        Application.DisplayAlerts = False
        On Error Resume Next
        ThisWorkbook.Worksheets(CStr(city)).Delete
        On Error GoTo 0
        Application.DisplayAlerts = True

        Set ws = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
        ws.Name = CStr(city)

        dataRng.AutoFilter Field:=3, Criteria1:=CStr(city)
        dataRng.SpecialCells(xlCellTypeVisible).Copy Destination:=ws.Range("A1")
        ws.Columns.AutoFit
    Next city

    src.AutoFilterMode = False
    src.Activate
    Application.ScreenUpdating = True
    MsgBox cities.Count & " city sheets created.", vbInformation
End Sub

It assumes Orders is a normal range with City in column C (convert a Table to a range first, or adapt the code to ListObjects). The result: sheets Pune, Nashik, Nagpur, Kolhapur, Solapur, Sambhaji Nagar.

D. Loop through all sheets

Sub ListAllSheets()
    Dim ws As Worksheet
    Dim i As Long
    For Each ws In ThisWorkbook.Worksheets
        i = i + 1
        Debug.Print i; ws.Name; " - used range "; ws.UsedRange.Address
    Next ws
End Sub

E. Combine sheets into one

Sub CombineCitySheets()
    Dim ws As Worksheet, dest As Worksheet
    Dim lastRow As Long, lastCol As Long, destRow As Long

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    On Error Resume Next
    ThisWorkbook.Worksheets("Combined").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True

    Set dest = ThisWorkbook.Worksheets.Add(Before:=ThisWorkbook.Worksheets(1))
    dest.Name = "Combined"
    destRow = 1

    For Each ws In ThisWorkbook.Worksheets
        Select Case ws.Name
            Case "Combined", "Orders", "Stores", "Products", "Lists", "Report"
                ' skip non-city sheets
            Case Else
                lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
                lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
                If lastRow >= 2 Then
                    If destRow = 1 Then        ' copy the header once
                        ws.Range(ws.Cells(1, 1), ws.Cells(1, lastCol)).Copy dest.Cells(1, 1)
                        destRow = 2
                    End If
                    ws.Range(ws.Cells(2, 1), ws.Cells(lastRow, lastCol)).Copy dest.Cells(destRow, 1)
                    destRow = destRow + (lastRow - 1)
                End If
        End Select
    Next ws

    dest.Columns.AutoFit
    Application.ScreenUpdating = True
    MsgBox "Combined " & (destRow - 2) & " rows into the Combined sheet.", vbInformation
End Sub

After SplitByCity and CombineCitySheets, the row count of Combined must equal the order count of Orders – a nice self-check.

Follow the formula or ribbon steps shown in this lesson (keep values numeric).

Quick revision cards

Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.