Here are 5 powerful Excel macros to automate tasks and improve productivity:
1. Auto-Format Excel Sheet
This macro formats the entire worksheet by adjusting column widths, adding bold headers, and setting
gridlines.
Sub AutoFormatSheet()
Dim ws As Worksheet
Set ws = ActiveSheet
' Auto-fit all columns
ws.Cells.EntireColumn.AutoFit
' Make the first row bold
ws.Rows(1).Font.Bold = True
' Apply gridlines
ws.Cells.Borders.LineStyle = xlContinuous
MsgBox "Sheet formatted successfully!", vbInformation
End Sub
2. Remove Duplicates from a Column
This macro removes duplicate values from Column A in the active worksheet.
Sub RemoveDuplicates()
Dim ws As Worksheet
Set ws = ActiveSheet
' Remove duplicates from column A
ws.Range("A:A").RemoveDuplicates Columns:=1, Header:=xlYes
MsgBox "Duplicates removed successfully!", vbInformation
End Sub
3. Save All Sheets as Separate Files
This macro saves each worksheet as a separate Excel file in the same folder as the main file.
Sub SaveSheetsAsFiles()
Dim ws As Worksheet
Dim folderPath As String
folderPath = ThisWorkbook.Path & "\"
For Each ws In ThisWorkbook.Worksheets
ws.Copy
ActiveWorkbook.SaveAs folderPath & ws.Name & ".xlsx", FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close False
Next ws
MsgBox "All sheets saved as separate files!", vbInformation
End Sub
4. Send Email with Attachment from Excel
This macro sends an email with the active workbook as an attachment using Outlook.
Sub SendEmail()
Dim OutlookApp As Object
Dim MailItem As Object
Dim wb As Workbook
Set OutlookApp = CreateObject("Outlook.Application")
Set MailItem = OutlookApp.CreateItem(0)
Set wb = ActiveWorkbook
With MailItem
.To = "recipient@example.com"
.Subject = "Automated Email from Excel"
.Body = "Please find the attached Excel file."
.Attachments.Add wb.FullName
.Send
End With
MsgBox "Email sent successfully!", vbInformation
Set OutlookApp = Nothing
Set MailItem = Nothing
End Sub
🔹 Tip: Ensure Outlook is installed and configured on your system.
5. Automatically Highlight Duplicate Values
This macro highlights duplicate values in Column A with a yellow background.
Sub HighlightDuplicates()
Dim ws As Worksheet
Dim rng As Range
Set ws = ActiveSheet
Set rng = ws.Range("A:A")
rng.FormatConditions.AddUniqueValues
rng.FormatConditions(1).DupeUnique = xlDuplicate
rng.FormatConditions(1).Interior.Color = RGB(255, 255, 0) ' Yellow
MsgBox "Duplicate values highlighted!", vbInformation
End Sub
How to Use These Macros?
1. Open Excel and press ALT + F11 to open the VBA Editor.
2. Click Insert > Module.
3. Copy and paste any of the macros into the module.
4. Close the VBA Editor and press ALT + F8 to run the macro.
Would you like any specific Excel automation macros? 😊
