useful macro codes

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”.

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.

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

List of 21 Useful Macros

excel useful macros codes list with pdf

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

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

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

4. Unhide All Hidden Worksheets

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

Here is the code for that

VBA Code

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 the active worksheet with other worksheets and then delete them.

VBA Code

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

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 an input box to enter a password.

Once you enter your password, click OK. And, make sure to take care about CAPS.

VBA Code

8. Convert All Formulas Into Values

Simply convet formulas into values.

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

VBA Code

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

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

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

12. Refresh All Pivot Tables

A super quick method to refresh pivot tables.

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

VBA Code

13. Resize All Charts in a Worksheet

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

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

VBA Code

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

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

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

17. Welcome Message for User

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

VBA Code

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

19. Insert a Linked Picture

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

And you can use that image anywhere you want.

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

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’s Next?

If you want to jump up your VBA skills I have a decent stuff for you. Sumit Bansal from TRUMPEXCEL has started a VBA Jetpack Course for you.

>>>Start your VBA Training Today<<<