Hello Excellers and welcome to another #macromonday blog post. Today I want to share with you a little VBA code that will speed how you rename worksheets in your Excel workbooks. In particular, if you have a lot of Excel workbooks in a folder this comes in really useful. I reused this macro last week as I had numerous Excel workbooks in a folder which represented different weeks of the financial year. Each workbook had one worksheet, and they all had to be renamed to ‘output’. Just the job for a little Excel macro. It saved a lot of clicking around with the mouse.
What Does The Macro Do?
It will re-name the active sheet “output”. This is really a super simple few lines of code as my workbooks only contain one worksheet and therefore the active worksheet is the default one. If you had more than one worksheet then you would need to click on the worksheet you want to rename to tell Excel that it is the active worksheet.
How Does The Macro Work?
The macro uses the ActiveSheet property. This represents the active sheet (the sheet on top) in the active workbook or in the specified window or workbook. So in this instance, it is the worksheet that we have currently selected in our workbook. The ActiveSheet.Name property returns or sets a string value that represents the active sheet name. We just give this value “output”.
First, we need to create a new module to save this Macro into. Insert a module by opening Visual Basic by hitting ALT+F11, or select the Developer Tab and in the Code Group Select Visual Basic. You can now hit Insert Module. I have chosen to store this in my Personal Macro Workbook so I can use with any workbook I want to.
If you want to read more about the Personal Macro Workbook then you can read my blog posts below
Macro Mondays -Creating and Updating Your Personal Macro Workbook
Macro Mondays – Create A Shortcut To Your Personal Excel Macro Workbook
So, if you want to save the macro in the workbook you are working on the insert the module into the current workbook. If you do this the macro will only be available in that particular Excel workbook.
The VBA Code
This really is a one-line macro where we simply set the ActiveSheet.Name to whatever string value we want. I am using “output”. Then all you need to to do is test the code!. This is my favourite part.
Want A Shortcut Key To Run The Code?
Let’s make this macro really easy to run. We can attach it to a keyboard shortcut key, or ‘assign’ a shortcut key.
- Choose Developer | Code | Macros (or press Alt+F8). The Macro dialog box appears
- Select the Macro name from the list
- Click the Options button. The Macro Options dialog box opens
- Assign the letter for the Shortcut Key
- Hit Ok
That’s it, folks. I hope you enjoyed this instalment of Macro Monday.
