Useful Excel Macros to Automate Repetitive Tasks

Performing repetitive and manual tasks in Excel can become tedious and time consuming. Fortunately, you can record and run macros to automate those tasks and save yourself time. Here are some of the most useful Excel macros to add to your toolkit with specific VBA code examples.

Format Large Batches of Data

If you need to format huge amounts of data - like applying number or date formats - do it with a macro. Record applying the formatting to one cell, then replay it on the entire sheet.

vb
Copy code
Sub FormatCells()
   Range("A1:A10").NumberFormat = "0.00"
End Sub

Clean Data

Quickly clean imported data by removing extra spaces, combining first and last names, adding leading zeros, and more using macros. Build multiple steps into one automated sequence.

Split Data into Multiple Sheets

Splitting large datasets into separate tabs or even separate files can be done quickly with a macro. Define your criteria and record the move to repeat on the rest of the data.

Apply Conditional Formatting

Applying conditional formatting rules repeatedly can be tedious. With a macro, just record yourself creating the rule once and designate the cell range to apply it to.

For example:

vb
Copy code
Sub ConditionalFormat()
   Range("A1:A10").FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ 
   Formula1:="=100"
   Range("A1:A10").FormatConditions(1).Interior.Color = vbYellow
End Sub

Create Charts and Reports

If you generate the same charts and reports manually, automate them with macros. You can produce multiple formatted reports in just one click.

And there are many other useful macros to automate repetitive tasks in Excel! Try recording and running macros yourself to save significant time.

Populate Data

Populate rows and columns with your desired inputs like names, IDs, email addresses and more based on criteria using a data population macro.

vb
Copy code
Sub PopulateData()
   Dim i As Integer
   For i = 2 To 100 
       Cells(i, 1).Value = "Name " & i
       Cells(i, 2).Value = "ID " & i
       Cells(i, 3).Value = "Email" & i & "@example.com"
   Next i
End Sub

Append Data from Files

Combine data from multiple Excel files into one master file automatically with a macro. Set the folder path and file criteria, and let the macro handle the rest.

vb
Copy code
Sub AppendFiles()
   Path = "C:\Reports"
   Filename = Dir(Path & "\*.xlsx")
   Do While Filename <> ""
      Workbooks.Open Filename:=Path & Filename  
      Range("A1:C10").Copy
      ThisWorkbook.Activate
      Range("A1").Offset(RowOffset:=1, ColumnOffset:=0).PasteSpecial
      Workbooks(Filename).Close
      Filename = Dir()
   Loop 
End Sub

Create a Backup

Configure a macro to save your important Excel files to another location for backup purposes on a schedule. Set it and forget it for peace of mind.

vb
Copy code
Sub AutoBackup()
   FileCopy ThisWorkbook.FullName, "C:\Backup"
   ChDir "C:\Backup"
   ActiveWorkbook.SaveAs Filename:="Backup " & Format(Now(), "yyyymmddhhmm")
End Sub

Excel macros allow you to turn repetitive manual processes into automated, one-click solutions.

Audit Spreadsheets

Run an audit on a spreadsheet before finalizing to flag errors, broken links, blank rows, and other issues. Fix problems before sending your reporting.

vb
Copy code
Sub AuditSheet()
   Dim cel As Range
   For Each cel In ActiveSheet.UsedRange  
       If IsError(cel) Then cel.Interior.Color = vbRed
       If cel.Value = "" Then cel.Interior.Color = vbYellow 
   Next cel
End Sub

Automate Formulas

Copying formulas across large spreadsheets is another repetitive task eliminated by macros. Record your formula once, designate the cell range, and automate calculation.

vb
Copy code
Sub CopyFormula()
   Range("B2").Formula = "=A2*2"
   Range("B2").AutoFill Destination:=Range("B2:B100"), Type:=xlFillDefault
End Sub

Format Data

Transform column data into a certain format, like converting phone numbers or dates into a standard pattern.

vb
Copy code
Sub FormatPhone()
   Dim cel As Range
   For Each cel In Range("A1:A10")
      cel.Value = Format(cel.Value, "000-000-0000") 
   Next cel
End Sub

Consolidate Data

Combine multiple worksheets into a master list by appending rows or columns from each sheet together.

vb
Copy code
Sub ConsolidateSheets()
   Dim sh As Worksheet
   For Each sh In Worksheets
       sh.Range("A1:C10").Copy
       Sheets("Master").Activate
       Range("A1").Offset(RowOffset:=1, ColumnOffset:=0).PasteSpecial 
   Next sh
End Sub

The possibilities are endless for how you can utilize macros to customize automation in Excel!

Create a Table of Contents

Dynamically generate a table of contents from sheet names or specified cell references.

vb
Copy code
Sub CreateTOC()
    Dim sh As Worksheet
    For Each sh In Worksheets
        ActiveSheet.Hyperlinks.Add Anchor:=Cells(sh.Index, 1), _
            Address:="", SubAddress:=sh.Name, TextToDisplay:=sh.Name
    Next sh
End Sub

Print Specific Sheets

Print only certain sheets in a workbook, like summarizing sheets or sheets for a certain team member.

vb
Copy code
Sub PrintSheets()
    Sheets(Array("Sheet1", "Sheet3", "Sheet5")).PrintOut
End Sub

Email Reports

Automatically email key reports as attachments to a recipient list on a schedule.

vb
Copy code
Sub EmailReport()
   ActiveWorkbook.SendMail Recipients:="email@example.com", _ 
       Subject:="Report for Today", Body:="Please see attached report."
End Sub

Protect Sheets

Restrict access to confidential sheets by adding a password requirement.

vb
Copy code
Sub AddPassword()
    Sheets("Private").Protect Password:="password123", _
        DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

Refresh Pivot Tables

Refresh all pivot tables with the latest data connection on demand.

vb
Copy code
Sub RefreshPivots()
   Dim pt As PivotTable
   For Each pt In ActiveSheet.PivotTables
      pt.RefreshTable
   Next pt
End Sub

Let me know if you need any other examples!

Find and Replace

Quickly find and replace text or values across an entire workbook.

vb
Copy code
Sub FindReplace()
   Cells.Replace What:="OldValue", Replacement:="NewValue", LookAt:=xlPart
End Sub

Insert Hyperlinks

Automatically insert hyperlinks to other worksheets, files or web URLs based on cell values.

vb
Copy code
Sub AddHyperlinks()
   Range("A1").Hyperlinks.Add Anchor:=Selection, Address:="http://example.com", _
       TextToDisplay:=Range("A1").Value
End Sub

Run Calculations

Recalculate all formulas when new data is added to automatically update outputs.

vb
Copy code
Sub CalculateSheet()
   Application.CalculateFull
End Sub

Clear Contents

Delete cell contents while keeping all formatting intact.

vb
Copy code
Sub ClearCells()
   Range("A1:B10").ClearContents
End Sub

Protect Worksheets

Lock cells or entire sheets to prevent accidental changes to important data.

vb
Copy code
Sub ProtectSheet()
   ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True 
End Sub

Let me know if any other examples for automating tasks in Excel using VBA would be helpful!

AutoSort Data

Automatically sort a data set by one or more columns.

vb
Copy code
Sub SortData()
    Range("A1:C10").Sort Key1:=Range("A1"), Order1:=xlAscending, _
        Key2:=Range("B1"), Order2:=xlDescending
End Sub

Create New Sheets

Dynamically add new sheets based on criteria like date, name or ID.

vb
Copy code
Sub AddSheet()
    Sheets.Add After:=Sheets(Sheets.Count) 
    ActiveSheet.Name = Format(Now(), "mmm-dd-yy")
End Sub

Remove Duplicates

Quickly remove duplicate rows in a data range while keeping the unique records.

vb
Copy code
Sub DeDupeData()
    Range("A1:C500").RemoveDuplicates Columns:=Array(1,2), Header:=xlYes
End Sub

Import Data

Pull data from external sources like SQL Server or another workbook.

vb
Copy code
Sub ImportData()
   Workbooks.OpenText "C:\File.csv", DataType:=xlDelimited
   Range("A1:C10").Copy 
   ThisWorkbook.Sheets("Sheet1").Range("A1").PasteSpecial
End Sub

Run a Loop

Iterate through a dataset row-by-row to perform a set of actions.

vb
Copy code
For i = 2 to 100
    'Actions here
Next i

Let me know if you need any clarification or have additional examples to automate!