Wednesday, March 18, 2009

Macros in Excel : Making your macro run every time

A macro records your mouse clicks and keystrokes while you work and lets you play them back later. You can use a macro to record the sequence of commands you use to perform a certain task. When you run the macro, it plays those exact commands back in the same order, causing Excel to behave just as if you had entered the commands yourself.

Certain precautions while recording a macro will help you generate a code which will run every time you are using that macro.

One major issue when you download data from source system for further processing is the sheet name. It is different every time you download data. Since the macro recorded by you will record the sheet name as available at the time of recording of macro. Same macro will not work next time as the sheet name for downloaded data will be different.

To make it work you need to change the sheet name to generic name as first step while recording your macro.

See the code below which is recorded to change the sheet name as first step. This helps to record the same name in macro in the next step when you flip through the sheets.

Sub ChangesheetName()
Sheets("Sheet1").Name = "Data"
End Sub

Once you have recorded above macro, small tweaking will help you to run this macro every time irrespective of the sheet name in downloaded data. You need to go to Visual Basic Editor and change the recorded macro as under

A small change in the first line of the code will help you run the macro without worrying about the sheet name of downloaded data.

Sub ChangesheetName()
ActiveSheet.Name = "Data"
End Sub

No comments:

Post a Comment

Learn how to create Excel dashboards.
Yogesh Gupta's Excel Tips