In this Excel Macros Tutorial, you are going to learn the Basics of Excel Macro. This is the first Article of this Tutorial so Let’s begin with What is Excel Macro
What is Excel Macro ?
Let’s not get too much in detail at this point. For now, in simple language, Macro is very popular software available with the most of the windows applications. Many of the windows applications provide inbuilt Macro programming. For example: Excel, Word etc. Here in this blog you will be learning this how to use it in Microsoft Excel.
It allows you to perform multiple operations just by clicking a simple button or changing a cell value or opening a workbook etc. It enables you to work in a smart and efficient way. In terms of productivity, it is very productive as it reduces lots of manual work and gets the things done very fast especially things which you are doing it repeatedly.
So, is it a programming language?
For now, Yes, it is very similar to Visual Basic. You don’t need to know much about the history of this language at this point of time. If you are interested and want to know history of this Language you can read this article. It is a video tutorial, where I have discussed about the history of this Language.
So now, I will directly jump to my original topic, where, I am going to teach you two Important features of this Tool, which, you as a beginner, will like it the most 🙂
They are
i) Recording a Macro
ii) Playing a Recorded Macro
Where do you find or see excel Macro or VBA code in Excel?
Don’t get confused with these two terms which I am using Macro and VBA. It is not worth spending time on. You can consider them to be the SAME THING for now. You can refer my video tutorial,, if you really want to know the difference between these two terms.
In this tutorial, I will be taking example of MS Excel and will be exploring each and every possible technique to do the things in a very easy manner.
In MS Excel, we have both recording and writing our own macros based on our need. We will start with Macro recording.
How to Record Macro in excel
In this section you will learn, how to Record Macro in Excel. Follow the below Steps one by one to record macros in Excel:
STEP 1.
Open one excel workbook. (Taken example of MS Excel 2010)
STEP 2.
Go to “Developer” Tab of the workbook. (How to add Developer Tab in Excel-2010 and 2007 Workbook)
STEP 3.
Click on “Record Macro” button in the right hand side of the Top Menu Bar
STEP 4.
In Excel 2007 and 2010, Macro recording can be started by clicking on the Red Shape button marked in the bottom bar of the Excel in left hand side.
STEP 5.
Click on Record Macro Button shown above
STEP 6.
In the above dialog box, enter the suitable name for this Macro.
You can also specify the shortcut key for running this particular recorded macro. It means on pressing that particular shortcut-key, this macro will automatically run.
To know more about assigning short keys to your macros read this article: How to assign a Shortcut key to a Macro ?
You can also refer list of important built-in short keys in Excel here
STEP 7.
You can give the location, where you want to store this particular macro and also you can write some description about this particular to be recorded Macro.
STEP 8.
Now click OK and do some operation on the excel sheet. Like formatting of the cell or sorting some of the values etc. and click on the Stop button at the same place when you started running the Macro.
STEP 9.
I have done the above formatting and wrote few column names while recording.
STEP 10.
To view this particular recorded macro, right click on any of the Sheet Name -> View Code
STEP 11.
In left hand side pane, under VBA Project of that workbook, Expand the Module
STEP 12.
One Module would have been created there “Module 1”. Click on this
STEP 13.
You can see the recorded code with the given Macro Name.
How to Run Excel Macro or VBA code in Excel
You can Run this Recorded Macro in two ways:
1. By Pressing Run Button
2. By Pressing a short key – Alt+F8
Before, I run this recorded Macro, I have deleted the formatting done during the recording of this macro. Now once I will run this Macro, then automatically that formatting will be done in that Sheet.. Magic.. Isn’t it? 🙂 First, we will see how to run the recorded Macro by pressing the play button in VB Editor screen.
Here is the clean sheet :
How to Run your macro by pressing the Play button?
STEP 1.
First of all, go to your VBA code screen. You would not find a play button on the workbook screen.
STEP 2.
Place your cursor/mouse within the excel macro which you want to run
STEP 3.
Click on Run or Play button as highlighted in below image:
That is it !! You are done… and here is the result:
Important
If you do not place your cursor somewhere within the VBA code which you want to run, then it will show you below popup with list of all Macros written in your workbook for your select which one you would like to run.
How to run play your macro without going to VBE Screen
If you Do not want to run the Macro, by Pressing the Run Button in the VBE screen, not to worry, you can run it from your Worksheet screen as well. Follow the below 3 simple steps to run your macro without going to the VBE screen.
STEP 1.
Press Alt + F8 as a Shortkey. The following popup will appear (same as mentioned above)
STEP 2.
Select Your Macro from the Drop Down
STEP 3.
Click on Run
That is it!! Congratulations !! Now, you know how to Record and Play Macro in Excel Workbook.
You may follow some tutorials here
- Beginner’s Page: Excel Macro Tutorial
- HP Quality Center: Excel Macros for HP Quality Center
- Excel Tutorial: Advanced Excel Macro Tutorials
- FREE Tools: Excel Macro Tools – FREE Download
- Send Email: Series of articles about sending emails using excel VBA
- UDFs : Useful User Defined Functions
- Excel Formula : Some important and useful Excel Formulas
This is amazing Vishwa. You have given a detailed description step by step.
I am a beginner and didn't even know what a macro is. I have created my first macro. Kudos to you!!!
You are Welcome Shagun and keep giving feedback 🙂
Thanks,
Vishwa
This is awesome. I just created my first Macro titled Macross 🙂
I feel like I have been taught to fish in Excel!
Your instructions are very thorough. The procedure worked on my first fly through!
Keep this up! I will definitely be back for more!
Thanks Johnny 🙂 🙂
Hai Vishwamitra, This is aswini. Could you please give your mail id??
Hi mishra
I have a macro file developed in excel 2003.But that file does not work properly in excel 2007.Some times it run.If i donot enter any data on that time macro work properly. What will be the reason
Hi Mishra,
I want to create one macro which can pick up only few fields out of 64 colmns and rows from an excel sheet.
Could u help me out for this ?
Hi Viswa,
This is really amazing .I truely appreciate your work. i have just started learning excel and this is te first macro i ve ever tried. Thanks a lot . i would like to learn a lot.
Thanks,
Mahesh.N
Thanks Mahesh!!
Hi Viswa,
I am using a excel document currently and all i wanted to do is b running a macro ,it should create a new excel document with copying the values from the document that i currently have. please help me with this.
Hi Mahesh,
If you are just trying to make a copy of the same workbook then use the following code: Let me know if this helps you.
Sub Creat_Copy()
Dim Wb As Workbook
Set Wb = ThisWorkbook
Wb.SaveCopyAs "E:LearnExcelMacro.comExcelFormattedhello.xls"
End Sub
HI Viswa,
1.i just opened the document
2.ALT+f11
3.insert->module
4.copied the script
5.changed the file location and ran it.
6.IT THOROWING A COMPILATION ERROR.
Please bear with me since i am a bigginer i am not so good at this. please help me.
Thanks.
What is the error?
Hi Viswa,
Thanks a lot, i correced the sntax error it worked fine …. hats off to you .. feeling great to reach you in case of any oter help. Please ignore the above two posts.
Thanks
Mahesh.N
Thanks Mahesh !!
good work man!!!!!!!
Thanks!!
Hi Viswa,
just like the above task i have to copy certain columns from a excel document and create a new excel document with help of a macro.Please help me with this
Regards,
Mahesh.N
chanceless vishwa helps me a lot as a new to excel i need to learn a lot need ur help:(
I would like to set up a macro in a workbook which automatically saves/closes the workbook after a specific period of time.
For example….if its idle for 5 mins without any activity it should give alert msg and close the excel file.
The trick is, the macro should only run if there has been no activity/changes to the workbook in that period of time.
If a workbook is accidently left open on a computer, this will close it automatically.
I have been searching for info on how to do this, and need help. Any advice is appreciated.
I am novice so step by step with programming will work for me…
Hi Vishwa,
I need to learn Excel macro cording,can you please help on this regards just give me small cordings with examples.I iknow recording I want to go to next step.
Hi Vishwa,
This is Yogisha.M you are doing wonder full job thank you for your suggestion, and I need one Branch Dash Board template (including all Parameters). Please help me on this.
hi I want to learn macro in excel kindly let me know how to work on macro
hai…i am a chemical engineer student..my lecturer give me assignment..where..user have to key in the x value..and the macro would provide the y value…its like an equation with x variable in it ..to give the y value to the user..can i know how to do it?? thanks..
I have a master sheet which have information of 10 of my employee and their task status in detail.
i need to run a macro which will identify these 10 employee and create new sheet as per there name.
which i have done with my below code:
————————————————————
Sub MAIN()
Dim MyCell As Range, MyRange As Range
Set MyRange = Sheets("Sheet1").Range("B2:B16")
Set MyRange = Range(MyRange, MyRange.End(xlDown))
For Each MyCell In MyRange
Sheets.Add after:=Sheets(Sheets.Count) 'creates a new worksheet
Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
Next MyCell
End Sub
——————————————————————–
now what i need to do is extra the rows with the employee name and put the row into the new sheet as per their name.
my macro is not running it's shows the error…
kindly learn me more macro demo
Hey its really cool website and really helpful for New People to learn Excel macro. I want to add your site on my amzingreviewz com . Please allow me to add u r site in my list
thanks
amazingreviewz team
It was reallly very helpful.
Thanks for sharing.
Hi.. U r doing a great job..I just want u to help me in inserting a photo/picture in my userform for each user..
Very easy and useful. It helps me alot. Thanks.
Hey, I have noticed that occasionally this site shows a 403 server error. I figured you would be keen to know. All the best
Hi Vishwa,
you have any book or CD or all these things. because i am a beginner and i need to work on Macros for 1 year.. But i dont no anything about it.. Can you please help me
Thanks
Thanks,for your consideration,,that is very helpfull for me,becouse i haven’t eny idea about macro ,but after follow up this E-book,,it helped me,,,thanks lot ,,again…so,,if you have any new tutorials regarding macro please mail link…
once i save the file and close it the module is not there,what shud be done if one wants to run the micro not in currnet time but after saving it and stuff.I hope u got my question.
Hi Mr. Khan,
It looks like while saving you must be getting a pop message like “do you want to save your file as a Macro-free Workbook” and if you say “Yes” then you will loose all VBA code written in a Module. On such message you say “No” and save your file as “.xlsm”. Hope this helps.
Hi,
i have got 5yrs of Mis Executive experience
and am looking for an institute to learn VBA Macros
pls suggest me
Institute name
what all courses need to be added
Thanks/ Srikanth
Hi Mishra,
i’m a beginner and wanna learn macro upto depth. I tried 2 subscriber but its not happening, can u please send me the e book.
HAISIVA, BASIC MACRO IN EXCEL SHEET SEND MU EMAIL ID :sadhamhusain15@gmail.com
hi Dear Vishwamitra Mishra.
really nice work, and I get a promotion due to your work
thanks a lot.
congratulations!! Kindly help me too and share some of the easier macros like exporting from sql database to excel and the steps to follow.
Hi Viswa,
i am a beginner i do not know anything much about macros.
but i have a requirement. it would be of great help if you could guide me with this.
the requirement is as follows:
1. i want to create a macro which would generate a work book which would have data in particular tabular format.
2. the column name should be fixed but few fields should be populated according to the values i provide i provide in the parent workbook
appreciate your time and help in advance.!!
Thanks,
Aisha
Hi Aisha/Vishwa,
If you got the help from vishwa, kindly share me the excel macro details or workbook and the steps which you followed. I too have the same requirement.It would be a great help for me to generate the workbook in tabular format.
Thanks in Advance!!
hi,
i have one q can any body help me… in my company we are using macros… by pressing 1 , 2 , 3 ,4 on excell the respective mail goes to manager.. so instead pressing 1 2, 3 i would like to put y or n. so how to runt can you help me
hai , i have seen the macro, it is very good and can understand it, but i have question on it , if we save a recorded macro in one excel file it can be used in another excel file ?
Hello
I am a beginner and i do not know anything about macros. But I want to learn macro in depth. what is the use and where I use…. Can u sent any tutorial material with examples to my mail id
Thanks..
Rams
Hi guys,
KIndly help me on macro.
you have any book or CD or any other source of Macro. because i am a beginner and i need to work on Macros.. But i dont know much about it.. I know how to record macro but I do not how to write macro Can you please help me
i make macro on change selection, it work when ever i change selection it run. but i want want to run macro when only particular cell selection change.
Please help me in this regard
my code is this:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set Target = Range(“H9”)
If Target.Value = “ACC” Or Target.Value = “ACC” Then
Call Hide_1
Else
Call UnHide_2
End If
End Sub
Hi
how can i give error message if the cell is selected No by a drop down list
i want error saying “Approval Mandatory- Please Attach”
Please help
Hi, I am a new learner in excel and want to learn macro from begining so could you please hel me on this …
Thanks in Advance
Rajesh k
Hello
I am a beginner and i do not know anything about macros. But I want to learn macro in depth. what is the use and where I use…. Can u sent any tutorial material with examples to my mail id
Thanks..
Rams
I am interested in learning about macros but get confused at the beginning about the syntax and how I can develop it for my own. could you please help me on how to develop the syntax.
Thanks Uche in Nigeria
Hello
I am a beginner and i do not know anything about macros. But I want to learn macro in depth. what is the use and where I use…. Can u sent any tutorial material with examples to my mail id
Thanks..
I am a beginner and i do not know anything about macros. But I want to learn macro in depth. what is the use and where I use…. Can u sent any tutorial material with examples to my mail id
Thanks..
How can we send emails from excel to gmail ,the name is selected from a dropdown and the email has to be sent
nice article
Hi Vishwamitra,
Hope you are doing good. Am little good in Excel but need to learn macros too as it is required in my current company. So please let me know some shortcut ways to learn macros completely.
Thanks and Best Regards,
Madhurjya Gogoi
E-Mail: madhurjya@nsiamerica.com
Hi Madhurjya,
Did you look at the material i have got for excel VBA tutorial page. Do provide your feedback on this? http://learnexcelmacro.com/wp/excel-macro-tutorial/