Why Every Excel User Needs These VBA Examples

These 10 VBA code examples represent the most commonly requested automation tasks in Excel. Each snippet is production-ready and can be customized for your specific needs. Whether you're processing data, generating reports, or managing files, these examples will save you hours of manual work.

1. Auto-Save Workbook Every 5 Minutes

Sub AutoSave()
    Application.OnTime Now + TimeValue("00:05:00"), "AutoSave"
    ActiveWorkbook.Save
    Application.StatusBar = "Auto-saved at " & Now()
End Sub

Use Case: Prevent data loss by automatically saving your work at regular intervals.

2. Clear All Empty Rows in Selection

Sub DeleteEmptyRows()
    Dim rng As Range, cell As Range
    Set rng = Selection
    
    For Each cell In rng.Columns(1).Cells
        If Application.WorksheetFunction.CountA(cell.EntireRow) = 0 Then
            cell.EntireRow.Delete
        End If
    Next cell
End Sub

Use Case: Clean up datasets by removing blank rows that interfere with analysis.

3. Convert Text to Proper Case

Sub ConvertToProperCase()
    Dim cell As Range
    
    For Each cell In Selection
        If Not IsEmpty(cell.Value) Then
            cell.Value = Application.WorksheetFunction.Proper(cell.Value)
        End If
    Next cell
End Sub

Use Case: Standardize text formatting in names, addresses, or product descriptions.

4. Create Summary Sheet from Multiple Worksheets

Sub CreateSummarySheet()
    Dim ws As Worksheet, summaryWS As Worksheet
    Dim lastRow As Long, summaryRow As Long
    
    Set summaryWS = Worksheets.Add
    summaryWS.Name = "Summary"
    summaryRow = 1
    
    For Each ws In Worksheets
        If ws.Name <> "Summary" Then
            lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
            ws.Range("A1:E" & lastRow).Copy
            summaryWS.Cells(summaryRow, 1).PasteSpecial xlPasteValues
            summaryRow = summaryWS.Cells(summaryWS.Rows.Count, 1).End(xlUp).Row + 1
        End If
    Next ws
End Sub

Use Case: Consolidate data from multiple worksheets into a single summary view.

5. Email Current Sheet as PDF

Sub EmailSheetAsPDF()
    Dim filePath As String
    Dim outApp As Object, outMail As Object
    
    filePath = ThisWorkbook.Path & "\" & ActiveSheet.Name & ".pdf"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=filePath
    
    Set outApp = CreateObject("Outlook.Application")
    Set outMail = outApp.CreateItem(0)
    
    With outMail
        .To = "recipient@example.com"
        .Subject = "Excel Report - " & ActiveSheet.Name
        .Body = "Please find attached the latest report."
        .Attachments.Add filePath
        .Send
    End With
End Sub

Use Case: Automatically distribute reports to stakeholders as professional PDF attachments.

6. Find and Replace Across All Worksheets

Sub FindReplaceAllSheets()
    Dim ws As Worksheet
    Dim findText As String, replaceText As String
    
    findText = InputBox("Enter text to find:")
    replaceText = InputBox("Enter replacement text:")
    
    For Each ws In Worksheets
        ws.Cells.Replace What:=findText, Replacement:=replaceText, _
                        LookAt:=xlPart, MatchCase:=False
    Next ws
    
    MsgBox "Find and replace completed across all worksheets."
End Sub

Use Case: Update company names, product codes, or any text across entire workbooks.

7. Protect All Worksheets with Password

Sub ProtectAllSheets()
    Dim ws As Worksheet
    Dim password As String
    
    password = InputBox("Enter protection password:")
    
    For Each ws In Worksheets
        ws.Protect Password:=password, DrawingObjects:=True, _
                  Contents:=True, Scenarios:=True
    Next ws
    
    MsgBox "All worksheets protected successfully."
End Sub

Use Case: Secure sensitive data by protecting multiple sheets simultaneously.

8. Import CSV Files from Folder

Sub ImportCSVFiles()
    Dim folderPath As String, fileName As String
    Dim ws As Worksheet
    Dim rowCount As Long
    
    folderPath = "C:\Data\" ' Update this path
    fileName = Dir(folderPath & "*.csv")
    rowCount = 1
    
    Set ws = ActiveSheet
    
    Do While fileName <> ""
        With ws.QueryTables.Add(Connection:="TEXT;" & folderPath & fileName, _
                               Destination:=ws.Cells(rowCount, 1))
            .TextFileParseType = xlDelimited
            .TextFileCommaDelimiter = True
            .Refresh
            rowCount = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 2
        End With
        fileName = Dir()
    Loop
End Sub

Use Case: Batch import multiple CSV files for consolidated data analysis.

9. Create Dynamic Charts from Data

Sub CreateDynamicChart()
    Dim chartRange As Range
    Dim myChart As Chart
    
    Set chartRange = Selection
    Set myChart = ActiveSheet.Shapes.AddChart2(240, xlColumnClustered).Chart
    
    With myChart
        .SetSourceData Source:=chartRange
        .HasTitle = True
        .ChartTitle.Text = "Dynamic Data Chart"
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).HasTitle = True
    End With
End Sub

Use Case: Quickly visualize data trends with professionally formatted charts.

10. Backup Workbook with Timestamp

Sub BackupWorkbook()
    Dim backupPath As String
    Dim timestamp As String
    
    timestamp = Format(Now(), "yyyy-mm-dd_hh-mm-ss")
    backupPath = ThisWorkbook.Path & "\Backup_" & _
                Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 5) & _
                "_" & timestamp & ".xlsx"
    
    ThisWorkbook.SaveCopyAs backupPath
    MsgBox "Backup created: " & backupPath
End Sub

Use Case: Create timestamped backups before making major changes to your workbook.

Implementation Tips

Test First: Always test VBA code on sample data before running on production files
Customize Paths: Update file paths and email addresses in the examples to match your environment
Add Error Handling: Include error handling for production use
Document Changes: Comment your modifications for future reference

Need Custom VBA Code?

While these examples cover common scenarios, your specific needs might require custom solutions. Our AI-powered VBA Code Generator can create tailored VBA code based on your exact requirements. Simply describe your task in plain English and get professional, tested code ready to implement.

Generate custom VBA code now - No programming experience required!

10 Essential VBA Code Examples Every Excel User Should Know