21 useful macros for excelDo you know with the help of these useful macros, you can break all the limitations of excel which you think excel has?

You can use these macro codes even if you haven’t used VBA before that.

All you have to do just paste these useful macros codes in your VBA editor.

These codes will exactly do the same thing which headings are telling you.

For your convenience, please follow these steps to add these codes to your workbook.

First of all, make sure you have your developer tab on your excel ribbon. If you don’t have please use these simple steps to activate developer tab.

  • Go to your developer tab and click on “Visual Basic”.

click on visual basic editor before you use these useful macros for excel

  • On the left side in “Project Window”, right click on the name of your workbook and insert a new module.

add module to paste these useful macros for excel

  • Just paste your macro into the module. And close it.
  • Now, go to your developer tab &  use these codes from macros.

Enjoy these 21 Useful Macros.

Download this PDF Version to learn more about these useful macros.

List of 21 Useful Macros

1. Create a Backup of a Current Workbook

This is one of the most useful macros which can help you to save a backup file of your current workbook. It will create a backup file in the same directory where your current file is saved.

And, it will also add the current date with the name of the file.

VBA Code

Sub FileBackUp()
ThisWorkbook.SaveCopyAs Filename:=ThisWorkbook.Path & _
"\" & Format(Date, "mm-dd-yy") & " " & _
ThisWorkbook.name
End Sub

2. Close All Workbooks at Once

Use this macro code to close all open workbooks. This macro code will first check all the workbooks one by one and close them.

If any of the worksheets is not saved, you’ll get a message to save it.

VBA Code

Sub CloseAllWorkbooks()
Dim wbs As Workbook
For Each wbs In Workbooks
wbs.Close SaveChanges:=True
Next wb
End Sub

3. Hide All but the Active Worksheet

Now, let’s say if you want to hide all the worksheets in your workbook other than the active worksheet. This macro is code will do this for you.

VBA Code

Sub HideWorksheet()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> ThisWorkbook.ActiveSheet.Name Then
ws.Visible = xlSheetHidden
End If
Next ws
End Sub

4. Unhide All Hidden Worksheets

And, If you want to un-hide all the worksheets which you have hide with previous code.

Here is the code for that

VBA Code

Sub UnhideAllWorksheet()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Visible = xlSheetVisible
Next ws
End Sub

5. Delete All but the Active Worksheet

If you want to delete all the worksheets other than the active sheet, this macro is useful for you.

When you run this macro it will compare the name of active worksheet with other worksheets and then delete them.

VBA Code

Sub DeleteWorksheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.name <> ThisWorkbook.ActiveSheet.name Then
Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = True
End If
Next ws
End Sub

6. Copy Active Worksheet Into a New Workbook

Let’s say if you want to copy your active worksheet in a new workbook, just run this macro code. And, it will do the same for you.

It’sthatsuper time saver.

VBA Code

Sub CopyWorksheetToNewWorkbook()
ThisWorkbook.ActiveSheet.Copy _
Before:=Workbooks.Add.Worksheets(1)
End Sub

7. Protect All Worksheet Instantly

Want to protect your all worksheets in one go. Here is a useful macro for you.

When you run this macro, you will get a input box to enter a password.

One you enter your password click OK.

And, make sure to take care about CAPS.

VBA Code

Sub ProtecAllWorskeets()
Dim ws As Worksheet
Dim ps As String
ps = InputBox("Enter a Password.", vbOKCancel)
For Each ws In ActiveWorkbook.Worksheets
ws.Protect Password:=ps
Next ws
End Sub

8. Convert All Formulas Into Values

This macro will do the same what I tell you here.

When you run this macro it will quickly change the formulas into absolute values.

VBA Code

Sub ConvertToValues()
Dim MyRange As Range
Dim MyCell As Range
Select Case MsgBox("You Can't Undo This Action. " & "Save Workbook First?", vbYesNoCancel, "Alert")
Case Is = vbYes
ThisWorkbook.Save
Case Is = vbCancel
Exit Sub
End Select
Set MyRange = Selection
For Each MyCell In MyRange
If MyCell.HasFormula Then
MyCell.Formula = MyCell.Value
End If
Next MyCell
End Sub

9. Remove Spaces from Selected Cells

One of the most useful macros from this list. It will check your selection and then remove extra spaces from that selection.

VBA Code

Sub RemoveSpaces()
Dim myRange As Range
Dim myCell As Range
Select Case MsgBox("You Can't Undo This Action. " & "Save Workbook First?", _
vbYesNoCancel, "Alert")
Case Is = vbYes
ThisWorkbook.Save
Case Is = vbCancel
Exit Sub
End Select
Set myRange = Selection
For Each myCell In myRange
If Not IsEmpty(myCell) Then
myCell = Trim(myCell)
End If
Next myCell
End Sub

10. Highlight Duplicates from Selection

This macro will check each cell of your selection and highlight the duplicate values from that selection.

You can also change the color from the code.

VBA Code

Sub HighlightDuplicateValues()
Dim myRange As Range
Dim myCell As Range
Set myRange = Selection
For Each myCell In myRange
If WorksheetFunction.CountIf(myRange, myCell.Value) > 1 Then
myCell.Interior.ColorIndex = 36
End If
Next myCell
End Sub

11. Hide All Pivot Table Subtotals

After creating a pivot table, if you want to hide all the subtotals, just run this code.

First of all, make sure to select any cell from in your pivot table and then run this macro.

VBA Code

Sub HideSubtotals()
Dim pt As PivotTable
Dim pf As PivotField
On Error Resume Next
Set pt = ActiveSheet.PivotTables(ActiveCell.PivotTable.name)
If pt Is Nothing Then
MsgBox "You must place your cursor inside of a PivotTable."
Exit Sub
End If
For Each pf In pt.PivotFields
pf.Subtotals(1) = True
pf.Subtotals(1) = False
Next pf
End Sub

12. Refresh All Pivot Tables

This is a super quick method to refresh a pivot table.

Just run this code and all of your pivot tables will be refresh in a single shot.

VBA Code

Sub RefreshAllPivotTables()
Dim ws As Worksheet
Dim pt As PivotTable
For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.RefreshTable
Next pt
Next ws
End Sub

13. Resize All Charts in a Worksheet

This macro code will help you to make all your chart of same size.

You can change the height & width of charts by changing it in macro code.

VBA Code

Sub Resize_Charts()
Dim i As Integer
For i = 1 To ActiveSheet.ChartObjects.Count
With ActiveSheet.ChartObjects(i)
.Width = 300
.Height = 200
End With
Next i
End Sub

14. Highlight the Active Row and Column

I really love this useful macro code whenever I have to analyze a data table.

Here are the quick steps to apply this code.

  • Open VBE (ALT + F11).
  • Go to Project Explorer (Ctrl + R, If hidden). Select your workbook & double click on the name of a particular worksheet in which you want to activate the macro.
  • Paste the code into it  & Select the “BeforeDoubleClick” from event drop down menu.
  • Close VBE & you are done.

Remember that, by applying this macro you will not able to edit the cell by double click.

VBA Code

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim strRange As String
strRange = Target.Cells.Address & "," & _
Target.Cells.EntireColumn.Address & "," & _
Target.Cells.EntireRow.Address
Range(strRange).Select
End Sub

15. Save selected range as a PDF

Select the range, run this macro and you will get a PDF file for that selected range.

It’s really cool.

VBA Code

Sub SaveAsPDF()
Selection.ExportAsFixedFormat Type:=xlTypePDF, OpenAfterPublish:=True
End Sub

16. Create a Table of Content

Let’s say you have more than 100 worksheets in your workbook. And, it’s hard to navigate now.

Don’t worry this macro code will rescue everything.

When you run this code it will a new worksheet and list the name of all worksheets with a  hyperlink to them.

VBA Code

Sub TableofContent()
Dim i As Long
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Table of Content").Delete
Application.DisplayAlerts = True
On Error GoTo 0
ThisWorkbook.Sheets.Add Before:=ThisWorkbook.Worksheets(1)
ActiveSheet.Name = "Table of Content"
For i = 1 To Sheets.Count
With ActiveSheet
.Hyperlinks.Add _
Anchor:=ActiveSheet.Cells(i, 1), _
Address:="", _
SubAddress:="'" & Sheets(i).Name & "'!A1", _
ScreenTip:=Sheets(i).Name, _
TextToDisplay:=Sheets(i).Name
End With
Next i
End Sub

17. Welcome Message for User

Want to deliver a message to your user every time he/she open a workbook.

VBA Code

Sub auto_open()
Dim name As String
name = Application.UserName
MsgBox ("Hello " & Application.UserName & ". Welcome To ExcelChamps.com")
End Sub

18. Active Workbook in an Email

Use this macro code to quickly send your active workbook in an e-mail.

You can change the subject, email & body text in code.

And if you want to send this mail directly use “.Send” instead of “.Display”.

VBA Code

Sub Send_Mail()
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.to = "[email protected]"
.Subject = "Growth Report"
.Body = "Hello Team, Please find attached Growth Report."
.Attachments.Add ActiveWorkbook.FullName
.display
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

19. Insert a Linked Picture

This VBA code will convert your selected range into a linked picture.

And you can use than image anywhere you want.

Sub LinkedPicture()
Selection.Copy
ActiveSheet.Pictures.Paste(Link:=True).Select
End Sub

20. Highlight Top 10 Values

You can also do the same thing by using conditional formatting options but this macro code is really quick.

Just select a range and run this macro and it will highlight top 10 values with green color.

VBA Code

Sub TopTen()
Selection.FormatConditions.AddTop10
 Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
 With SelectionWithFormatConditions(1)
 .TopBottom = xlTop10Top
 .Rank = 10
 .Percent = False
 End With
 With Selection.FormatConditions(1).Font
 .Color = -16752384
 .TintAndShade = 0
 End With
 With Selection.FormatConditions(1).Interior
 .PatternColorIndex = xlAutomatic
 .Color = 13561798
 .TintAndShade = 0
 End With
 Selection.FormatConditions(1).StopIfTrue = False
End Sub

21. It’s Your Turn

Hey, It’s your turn to complete this list.

If you have any VBA code which you use every day to work smarter, please share that code with me in the comment box.

And, if you have any friend who is a VBA beginner, please share these useful macros with him.

What Can I Do Next?

Do you want to master VBA, Here I have a master plan for you.

VBA Jetpack Course By Sumit Bansal (Trump Excel)

>>Click Here To Read More<<