🔥 5 Best VBA Codes Ever for Daily Excel Work

These aren’t just cool tricks — they’re practical, time-saving VBA macros you’ll actually use. Copy, paste, and automate your daily Excel life!

✅ Why Use VBA in Excel?

VBA lets you automate tasks like formatting, data cleaning, sending emails, creating reports, and more — all at the click of a button.

Imagine replacing 30 minutes of manual work with a 3-second macro!

📌 1. Auto-Save Workbook Every X Minutes

Perfect for: Preventing data loss

Private Sub Workbook_Open()

Application.OnTime Now + TimeValue("00:10:00"), "AutoSave"

End Sub

Sub AutoSave()

ThisWorkbook.Save

Application.OnTime Now + TimeValue("00:10:00"), "AutoSave"

End Sub

How it works:
When you open the workbook, it auto-saves every 10 minutes.

👉 Pro tip: Add this in the ThisWorkbook object in VBA.

📌 2. Convert All Sheets’ Ranges to Tables

Perfect for: Making data analysis-ready with structured tables

Sub ConvertAllRangesToTables()

Dim ws As Worksheet

Dim tbl As ListObject

For Each ws In ThisWorkbook.Sheets

If ws.UsedRange.Cells.Count > 1 Then

Set tbl = ws.ListObjects.Add(xlSrcRange, ws.UsedRange, , xlYes)

tbl.Name = "Table_" & ws.Index

End If

Next ws

End Sub

Why this rocks: Converts raw data into Excel Tables across multiple sheets, instantly enabling filters and formulas.

📌 3. Send Outlook Email Directly from Excel

Perfect for: Daily reports, client updates

Sub SendEmail()

Dim OutApp As Object

Dim OutMail As Object

Set OutApp = CreateObject("Outlook.Application")

Set OutMail = OutApp.CreateItem(0)

With OutMail

.To = "client@example.com"

.Subject = "Daily Report"

.Body = "Hi, please find today's report attached."

.Attachments.Add "C:\Users\YourName\Documents\report.xlsx"

.Send

End With

Set OutMail = Nothing

Set OutApp = Nothing

End Sub

Why use this? Automate emailing without ever leaving Excel!

📌 4. Highlight Duplicate Values Instantly

Perfect for: Data cleaning and validation

Sub HighlightDuplicates()

Dim rng As Range

Set rng = Selection

rng.FormatConditions.AddUniqueValues

With rng.FormatConditions(rng.FormatConditions.Count)

.DupeUnique = xlDuplicate

.Font.Color = RGB(255, 255, 255)

.Interior.Color = RGB(255, 0, 0)

End With

End Sub

Use case: Select your data → Run macro → Duplicates get red-highlighted.

📌 5. Create a Backup Copy of Your Workbook

Perfect for: Version control & protection

Sub BackupWorkbook()

Dim FilePath As String

FilePath = ThisWorkbook.Path & "\" & _

"Backup_" & Format(Now, "yyyymmdd_hhmmss") & ".xlsm"

ThisWorkbook.SaveCopyAs FilePath

MsgBox "Backup saved at: " & FilePath

End Sub

Pro tip: Add a button to run this anytime before heavy edits.

🧠 Final Thoughts: Boost Productivity With VBA

You don’t need to be a coder to use VBA — just copy, paste, and save time.

Start with one macro, assign it to a button or shortcut, and feel the power of automation in Excel.

Blogs teach. Videos show