How to Write Macros in Excel 2010

1. Open Excel on your computer. Click the 'View' tab on the ribbon and then click the 'Macros' icon.
2. Enter a descriptive name for the new macro. Do use spaces in the macro name. Create a sample macro to use during the process of learning to write a macro in VBA. Therefore, enter the macro name 'Change_Worksheet_Names' in the macro name field and then click 'Create.' The Microsoft Visual Basic for Application window opens and displays a 'Module (Code)' window with the blank 'Sub Change_Worksheet_Names()' code document.
3. Continue creating the sample macro, which changes all of the generic 'Sheet1,' 'Sheet2' worksheets in a workbook to a value from a header or title cell in each worksheet. For example, if you enter the title for each sheet in cell 'A1' of each worksheet, this macro changes the name displayed at the bottom of each worksheet to the value in the referenced cell -- in this case 'A1.' To create the macro using VBA, enter the following code between the 'Sub Change_Worksheet_Names()' and 'End Sub' tags:Dim myWorksheet As WorksheetFor Each myWorksheet In Worksheets'The next line of code verifies that cell A1 in each worksheet is not empty. This text is a code 'remark.' The single quote mark at the beginning of the line informs Excel not to include this text in the macro code. Remarks are a good way to leave notes in VBA code that explain commands or syntax used.If myWorksheet.Range('A1').Value
'' Then'This command renames the worksheet to the text value in cell 'A1' of the first worksheet.myWorksheet.Name = myWorksheet.Range('A1').ValueEnd If'The 'Next' command instructs Excel to repeat the above code commands until it has finished renaming all the worksheets in the active workbook.Next
4. Click the 'Save' icon beneath the menu bar in the VBA editor window. Enter a name for the Excel template that contains the macro code. Select 'Excel Macro-Enabled Workbook' as the file type and then click 'Save.' Close the VBA editor window.
5. Test the macro for the sample VBA code. Enter a header or sheet title name in cell A1 of each worksheet. For instance, enter 'Daily,' Weekly' and 'Monthly' in cell A1 of three worksheets. The tabs for each worksheet at the bottom of the Excel window should read 'Sheet1,' 'Sheet2' and 'Sheet3,' respectively.
6. Click 'Macro' on the 'View' tab. Highlight the 'Change_Worksheet_Names' macro and then click 'Run.' The worksheet tab names at the bottom of the window change to 'Daily,' 'Weekly' and 'Monthly.'