VBA Cheat Sheet for Accountants

When to use: After pasting raw data from your accounting system — instantly make it presentation-ready.

Sub FormatReport()
  With ActiveSheet.UsedRange
    .Font.Name = "Calibri": .Font.Size = 10
    .Columns.AutoFit
    .Rows(1).Font.Bold = True
    .Borders.LineStyle = xlContinuous
  End With
End Sub

When to use: During bank reconciliation or invoice reviews — spot duplicates instantly instead of scanning manually.

Sub HighlightDuplicates()
  Dim rng As Range: Set rng = Selection
  rng.FormatConditions.AddUniqueValues
  rng.FormatConditions(rng.FormatConditions.Count).DupeUnique = xlDuplicate
  rng.FormatConditions(rng.FormatConditions.Count).Interior.Color = RGB(255, 200, 200)
End Sub

When to use: When consolidating monthly or departmental totals — no more clicking through each sheet manually.

Sub SumAcrossSheets()
  Dim ws As Worksheet, total As Double
  For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> ActiveSheet.Name Then
      total = total + ws.Range("B10").Value
    End If
  Next ws
  ActiveCell.Value = total
End Sub

When to use: When preparing journals or ledgers — add sequential reference numbers down a column in one click.

Sub NumberTransactions()
  Dim i As Long, lastRow As Long
  lastRow = Cells(Rows.Count, "B").End(xlUp).Row
  For i = 2 To lastRow
    Cells(i, "A").Value = "TXN-" & Format(i - 1, "0000")
  Next i
End Sub

When to use: After importing data that lost its formatting — apply consistent currency format to selected cells.

Sub FormatAsCurrency()
  Selection.NumberFormat = "$#,##0.00"
  Selection.HorizontalAlignment = xlRight
  Selection.Font.Name = "Calibri"
  Selection.ColumnWidth = 14
End Sub

When to use: During expense reviews or audits — highlight any amounts that exceed a set threshold for quick review.

Sub FlagOverThreshold()
  Dim cell As Range, limit As Double
  limit = InputBox("Enter threshold amount:", , 10000)
  For Each cell In Selection
    If IsNumeric(cell.Value) And cell.Value > limit Then
      cell.Interior.Color = RGB(255, 235, 156)
    End If
  Next cell
End Sub

When to use: After data imports leave gaps — clean up empty rows to make your data contiguous and sortable.

Sub RemoveBlankRows()
  Dim i As Long
  For i = ActiveSheet.UsedRange.Rows.Count To 1 Step -1
    If Application.CountA(Rows(i)) = 0 Then
      Rows(i).Delete
    End If
  Next i
End Sub

When to use: When setting up workbooks for multiple clients or periods — generate named sheets automatically.

Sub CreateMonthlySheets()
  Dim months As Variant, m As Variant
  months = Array("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")
  For Each m In months
    Worksheets.Add(After:=Sheets(Sheets.Count)).Name = CStr(m)
  Next m
End Sub

When to use: Before sharing workbooks with clients or colleagues — lock headers so they can't be accidentally edited.

Sub ProtectHeaders()
  Rows(1).Locked = True
  ActiveSheet.UsedRange.Offset(1).Locked = False
  ActiveSheet.Protect Password:="edit"
  ActiveWindow.FreezePanes = False
  Rows(2).Select: ActiveWindow.FreezePanes = True
End Sub

When to use: When you need to send data to another system — export a selected range to a clean CSV file.

Sub ExportAsCSV()
  Dim path As String, r As Range, line As String
  path = ThisWorkbook.Path & "\export_" & Format(Now, "yyyymmdd") & ".csv"
  Open path For Output As #1
  For Each r In ActiveSheet.UsedRange.Rows
    line = "": Dim c As Range
    For Each c In r.Cells: line = line & c.Text & ",": Next c
    Print #1, Left(line, Len(line) - 1)
  Next r: Close #1
End Sub