Macros In Office Excel 2010

We will discuss the following in this post:

  • What are Macros?
  • How to create Macro in Excel 2010
  • What is Relative References
  • How to run a Macro in Excel 2010

What are Macros

Do you often perform the same repeated tasks again and again in Excel? A macro records your mouse clicks and keystrokes while you work and play them back later. It is used to record the sequence of actions you use to perform a certain task. When you run the macro, it plays those actions back in the exact same order. Thus, saving you headache if you want to repeat the actions multiple times.

Create/Record Macros In Excel 2010

Hit the View menu and choose the Macros > Record Macro.

Record Macro

The Record Macro dialogue box will be displayed, here specify the details of the Macro. Macro names must start with a letter and can include letters, numbers, and underscore characters. You can specify the shortcut key to run the Macro here, also for better understanding of the Macro specify the Macro description.

Record Macro Dialgue box That’s it, now perform your steps and Excel will keep recording all the steps, when you are done, stop the Macro recording from the Macro > Stop Recording option.


Macro recording stop

What is Relative References

By default, Excel Macro Recorder records your absolute steps. Lets suppose you want a to move from cell A8 to B8 after performing an action, you will press the Right Arrow key, but Excel will not record this key, instead it will only record the movement to cell B8. Now lets suppose you have recorded the actions and are to perform it in cell C8, when you run the Macro, a line of Macro will be executed and then cell B8 will be selected instead of selecting cell D8(which is to the right of C8).

Like I said above, this is because Excel records the movement to cell B8 instead of recording every action(keystroke). If you want Excel to record relatively, so that Excel moves to the right cell instead of selecting cell B8, you will have to enable Relative References. It can be switched on from the Macros menu just below the Start/Stop Recording option.

Run Macros In Excel 2010

Once you have recorded the Macro, you will not need to perform the repeated steps again and again any more. Hit the Macro >  View Macros option and it will display the list of all the created/recorded Macros. Choose your desired Macro and hit the Run button. You can also edit and delete Macros from here.

Run Macro

A much more easier method is to hit the shortcut key(hotkey). If you forgot to assign a shortcut key, simply head over to View Macro, select the desired Macro and hit Options, now assign a hotkey and hit OK.

The third method is even more easier if you are not a hotkey junkie, simply add the Macro to the Quick Access Toolbar. Now one click is all that is needed to execute a macro.

Advertisement
  • Pingback: VBA In Office Excel 2010

  • question

    In excel 2010 is possible set custom macro to formula???

  • Dan

    A fourth way to run a macro is to use a form control button. On the Developer ribbon choose insert, select form and click on the location on the worksheet you wish to add the button. Right click on the button, click on assign macro, select the macro you wish ot asign and click OK

  • greg patterson

    Anyone heard of several macros failing after this week’s Win Updates. If I open a files from last year, the macro works, but not the current macro. Specifically a ” Dim Responsehowpd As String
    is completely bypassed after the damn update.

  • bobby

    Can I combine conditioning formatting and other functions into a macros, when I start recording I change basic stuff (Fonts, centring, deletion etc, and then I go to conditioning formatting add 2 conditions and stop recording. the macros records anything but the conditioning formatting, what are I doing wrong

  • Mgibbons1946

    Can I Set a macro to GOTO a cell that is referenced in another cell?

  • ramadevi

    how to usage excel in HR ple give me imformation.     rama

  • kg

    I need to delete the first digit of the cell in over 8000 cells (e.g. change ’10001 to 10001, ’10002 to 10002) .  I created a macro, but the cell value isn’t maintained when running the macro.  Instead, the value from the cell in which I created the macro (e.g. 10001) re-appears.  I’ve used relative references.  How can I resolve this?

  • kg

    I need to delete the first digit of the cell in over 8000 cells (e.g. change ’10001 to 10001, ’10002 to 10002) .  I created a macro, but the cell value isn’t maintained when running the macro.  Instead, the value from the cell in which I created the macro (e.g. 10001) re-appears.  I’ve used relative references.  How can I resolve this?

    • JJ

      You could use the find and replace (ctrl+h) and replace all ” ’1 “s with “1″s

    • Skyyblue7204

      Use a V Lookup

    • Ashish

      You can also use text to column command to curtail first digit or can use left or right function.

  • Ganthimani

    Hi I have Excel 2010 in my system, I cauldn’t open and work in macro. By defaulf Macro Options like Record Macro and Stop Macro is disabled in my machine.. Is der any solution to start this by myself?

  • Deborah Dowd

    I am Office 2010 Macro illiterate.  I want  a Macro that will open to the Instructions Tab each time a worksheet is open.  Can someone help?

  • Mangal Singh Negi

    sir plz send me excel 2010 formula and function. And I want connect to two cell table to a cells as change one cell digit that change to other cell required digit accordingly to table.plz give me help in mail i.d. negishikha88@gmail.com

  • Ryan Waters

    Hi All, I need to create a macro that pulls the worksheet tab name from different yet specific tab, any ideas would be greatly appreciated!!

  • billy

    how can I run a macro without displaying it step by step to the user?

  • Morris

    I have a macro in Sheet2 that reads information from Sheet1, how can I do that if I change information in Sheet1, that changes are displayed in the next row down in sheet2?

  • Lulu

    I need to create a Macro that can be used in different named tabs. EG. I have a tab for each day of the week, do I need to create the Macro for each day or can I use the same Macro for each day? This problem came up when I switched to Excel 2010, it was working with 1 Macro in previous versions

  • rlehman@allcleveland.com

    I am trying to incorpate page setup selections within a Macro. It works up until the Header/Footer information. I cannot get the auto text (i.e. sheet tab name, page numbering) to work within the macro. I was able to do this with prior versions of Excel but not with 2010 which I am currently using. Any suggestions??