Excel Macro Examples To Add Powerful Macros to Your Worksheets

Excel macros are powerful subroutines that you can create to save you a lot of time when it comes to your spreadsheets. If you spend a lot of your time working on Excel and a lot of your tasks are repetitive, then this tutorial may be just what you are looking for. This tutorial will show you how to create a macro by recording your tasks so that you can perform them at the click of a button.

If you want to learn how to harness the power of macros in your spreadsheets and worksheets right now, then the Excel VBA and Macros with MrExcel course offers fifty video tutorials that will teach you everything you need to know to start creating your own macros in Excel.

For the purposes of this tutorial, we will use a worksheet based on a fictitious grocery store. Our grocery store keeps track of daily sales using a new spreadsheet every day. This tutorial assumes that you know how to work with Excel, how to add spreadsheets to a worksheet and how to use the basic functions and formulas available in Excel. If you would like to learn how to get the most out of Excel, the Excel 2013 Made Easy course from Udemy will help you create a strong foundation for using Excel.

To keep track of our sales we need to create a spreadsheet each morning that contains all of the sales items we sell. The worksheet starts with today’s date. We are going to create a few macros to help us create the sheet each morning without having to retype the sheet or add the values to each new sheet.

How to Create Macros in Excel

To create a macro in Excel you need to use the developer tab. For a tutorial on how to access the developer tab and create your first macro, read Excel VBA Tutorial: Make Your First Macro and Get to Know the Tools.

To create our daily spreadsheet, we are first going to add a template sheet to copy the format of our daily sheet. This is what our template sheet looks like:

How to Create a Macro to Add a New Worksheet

The first macro we are going to create will add a new sheet, and name that sheet based on a value we enter via an input box.

To create a new sheet, we will use the macro recorder to create a macro that adds a new sheet. For a tutorial on how to create your first simple macro, you can read Excel Macros Tutorial: How to Create a Simple Macro in Excel 2013 or if you would like a step by step course on how to create macros, then sign up for the Ultimate Excel VBA course.

To create our macro to add a sheet, click the Developer tab and then click record macro.

We will name our Macro “AddSheet” and assign “a” to the shortcut key for our new macro:

Now we will click the worksheets tab to add a new sheet. Once the new sheet has been added, we will stop recording. This will create a macro for us that will automatically add a new sheet to our worksheet. We need to edit the macro so that the macro asks us for a name for the new sheet.

To edit a macro, click the Macro button, select the “AddSheet” Macro and then click edit.

Next we need to add an InputBox to our code so we can get the new sheet name from the user. The Excel VBA InputBox Tutorial for Allowing User Input in Excel tutorial, will show you how to work with Excel input boxes.

We will add the following code to our macro to name the new sheet using user input:

This above code creates a variable to store the user name we receive from the Input box. Next we assign the name of the new sheet as the value stored in the variable.

Create a Macro to Copy Cell Formats

Now that we have a new sheet, we need to copy the cell formats from our template sheet. If you need to learn how to copy and paste or use other more advanced functions in Excel, then you can read Advanced Excel Skills You Should Know.

To copy the borders, fonts etc, we are going to create a new macro. To create a new macro, we are going to start by selecting the new sheet we have created. We are then going to record a new macro, call the macro “CopyFormat” and select f as our shortcut key:

Once we select okay to start recording the macro, we will select our template worksheet, select the total template worksheet and then select copy from the menu or hold CTRL C to copy the cells. The macro will record each instruction we give Excel so that it can repeat the steps for us when we run the macro.

Now we want to copy the cell formats into our newly created sheet. To copy the formats of the cells, select the entire worksheet and then right click the worksheet and select paste special.

Your cell formats will be copied to your newly named sheet:

Now we have to decide how we want to create our column names. There are two ways to create the new column names. We can either copy the names and paste them into our new sheet, or we can use a formula to create column names that are equal to our template sheet. If we choose to copy and paste the column names, then the column names will be copied as text. If we change the column names in the template sheet, the new sheets will not change. If we use a formula, the new column names will depend on the template sheet column names and if we change the column names in the template sheet, all sheets will change.

For our example we will use the formula to create our column names:

Now we are going to copy that formula to each of the columns to add the column names for each column:

All we need to do now is copy the items and prices. For each new sheet we will use the copy paste technique to add the items and columns. This means that the values of the cells will be copied. This means that new sheets will not be changed if we change our template sheet so it will record our sales at the price for that day.

To copy the values, select the “Template” worksheet, select the range of values you want to copy, then select the new worksheet and select the range you want to copy the values to and click CTRL V to paste the values. Repeat these steps to copy the prices of the items to the new sheet.

We have now created a macro that will copy the formats and values into our new sheet. All we have to add is the formulas to calculate the Sales and Total Sales column.

Create a Macros to Add Formulas to our Worksheet

To create a Macro for our formulas, click record and call the new macro “Add Formulas” and assign “g” to the macro:

Add the formula to calculate the total sales for each row by typing:

This multiplies the sales by the price to calculate the total sales. Copy this formula to each of the row items and then click stop recording to create the macro.

Running our Daily Macro

Instead of creating a new sheet from scratch each day, you now have three macros that will easily create the daily sheet you need. All you need to do is run each macro to create your sheet. To run the macros, click CTRL “a” to create a new sheet and name it. Then click CTRL “f” to copy the formats and finally click CTRL “g” to add the formulas.

If you want to learn to master Excel, sign up for the The Ultimate Microsoft Excel 2013 Training Bundle course from Udemy. This course offers over 19 hours of video tutorials, over 100 video lectures and you get a certificate of completion for completing the course.