Do 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”.
- On the left side in “Project Window”, right click on the name of your workbook and insert a new module.
- 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.
List of 21 Useful Macros
- Create a Backup of a Current Workbook.
- Close All Workbooks at Once.
- Hide All but the Active Worksheet.
- Delete All but the Active Worksheet.
- Unhide All Hidden Worksheets.
- Copy Active Worksheet Into a New Workbook.
- Protect All Worksheets.
- Convert All Formulas Into Values.
- Remove Spaces from Selected Cells.
- Highlight Duplicates from Selection.
- Hide All Pivot Table Subtotals.
- Refresh All Pivot Tables.
- Resize All Charts in a Worksheet.
- Highlight the Active Row and Column.
- Save selected range as a PDF.
- Create a Table of Content.
- Welcome Message for User.
- Active Workbook in an Email.
- Insert a Linked Picture.
- Highlight Top 10 Values.
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.
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.
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.
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
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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”.
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.
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)