Dec 05, 2018 How can I rename worksheet using cell data. Put the following event macro in the worksheet code area: Private Sub Worksheet_Calculate()..Name = s End Sub. Because it is worksheet code, it is very easy to install and automatic to use: 1. Right-click the tab name near the bottom of the Excel window 2. Select View Code - this.
The tutorial shows how you can quickly sort Excel worksheets in alphabetical order by using VBA code and the Workbook Manager tool.
Microsoft Excel provides a number of quick and easy ways to arrange columns or rows in alphabetical order. But there is only one method to rearrange worksheets in Excel - drag them to the desired position on the sheet tab bar. When it comes to alphabetizing tabs in a really large workbook, this may be a long and erroneous way. Looking for a time-saving alternative? There exist only two: VBA code or third-party tools.
- Alphabetize tabs in Excel with VBA
How to alphabetize tabs in Excel with VBA
Below you will find three VBA code examples to sort Excel sheets ascending, descending, and in either direction based on the user's choice.
Implying that you have some experience with VBA, we will only outline the basic steps to add a macro to your worksheet:
- In your Excel workbook, press Alt + F11 to open the Visual Basic Editor.
- On the left pane, right-click ThisWorkbook, and then click Insert > Module.
- Paste the VBA code in the Module window.
- Press F5 to run the macro.
For the detailed step-by-step instructions, please see How to insert and run VBA code in Excel.
Tip. If you want to keep the macro for further usage, be sure to save your file as an Excel macro-enabled workbook (.xlsm).
Alternatively, you can download our sample Alphabetize Excel Tabs workbook, enable content if prompted, and run the desired macro directly from there. The workbook contains the following macros:
- TabsAscending - sort sheets alphabetically from A to Z.
- TabsDescending - arrange sheets in the reverse order, from Z to A.
- AlphebetizeTabs - sort sheet tabs in both directions, ascending or descending.
With the sample workbook downloaded and open in your Excel, open your own workbook where you want to alphabetize tabs, press Alt + F8, select the desired macro, and click Run.
Sort Excel tabs alphabetically from A to Z
This little macro arranges the sheets in the current workbook in ascending alphanumeric order, first worksheets whose names start with numbers, then sheets from A to Z.
Arrange Excel tabs from Z to A
If you want to sort your sheets in descending alphanumeric order (Z to A, then sheets with numeric names), then use the following code:
Alphabetize tabs ascending or descending
This macro lets your users decide how to sort worksheets in a given workbook, alphabetically from A to Z or in the reverse order.
Since the standard dialog box (MsgBox) in Excel VBA only allows choosing from a handful of predefined buttons, we will create our own form (UserForm) with three custom buttons: A to Z, Z to A, and Cancel.
For this, open the Visual Basic Editor, right-click ThisWorkbook, and click Insert > UserForm. Name your form SortOrderFrom, and add 4 controls to it: a label and three buttons:
Next, press F7 (or double-click the form) to open the Code window and paste the below code there. The code intercepts button clicks and assigns a unique tag to each button:
Depending on whether the user clicks the A to Z or Z to A button on your form, sort tabs in ascending alphabetical order (selected by default) or descending alphabetical order; or close the form and do nothing in case of Cancel. This is done with the following VBA code, which you insert in the usual way via Insert > Module.
If you are not very comfortable with VBA yet, you can simply download our Sample Workbook to Alphabetize Tabs, open it in your Excel alongside your own file where you want to sort tabs, and run the AlphebetizeTabs macro from your workbook:
Choose the preferred sort order, say, A to Z, and observe the results:
How to sort Excel tabs alphabetically with Ultimate Suite
The users of our Ultimate Suite for Excel don't have to fiddle around with VBA - they have a multi-functional Workbook Manager at their disposal:
![Excel macro to label sheet tab from cell value excel 15 for mac pro Excel macro to label sheet tab from cell value excel 15 for mac pro](https://www.airweb.org/eAIR/techtips/PublishingImages/tech%20tip%20excel%20image.jpg)
With this tool added to your Excel ribbon, alphabetizing tabs is done with a single button click, exactly as it should be!
If you are curious to explore this and 60+ more professional tools for Excel, a trial version of our Ultimate Suite is available for download here.
I thank you for reading and hope to see you on our blog next week!
You may also be interested in:
-->
This article was written by Raddini Rahayu, Microsoft MVP.
Case
Here is weekly sales activity reports. In example, there are 15 person in this case. Nancy, the admin reports, she is assigned summarizing each sales data into one file where all sales are separated on each sheet. For easly arranging data, each sheet she gave the name matches the sales name on that sheet. At first, Nancy feel comfortable with this, but as more and more data and demands rapid processing, she was so overwhelmed. To overcome this, Nancy want to the name sheets changed automatically according to the sales name on each sheet without manually rename it.
Solutions
The best solutions to overcome the Nancy's problem is by using macro. This macro designed for each sheet in that file regardless of the number of sheet. The name of each sheet will change according to the sales name that have been determined in the same location on each sheet.
How to Execute
First Step
-
Designing report formats and specify the cell location where the sales name will be placed. In this case the location is at cell J2. This report format should be the same on each sheet.
-
Keep the sheet names on the default name (Sheet1, Sheet2, Sheet3, etc).
Last Step
Macro Coding Step
After we know the cell location where the sales name will be placed, now we can continue the next step, Macro coding.
- On the Developer tab Code category select Visual Basic, or press key combination Alt+F11on the keyboard, so Visual Basic window is displayed.
-
On the project taskpane click Sales Report.xlsx(Workbook Name), then on Insertmenu select Moduleand write the following script:
-
Press the F5key on the keyboard, if there is no debug close the Visual Basic window and return to Excel. If there is debug, check back your script.
-
Return to Excel and see what happens, if the codes are correct, now the name of each sheet is renamed in accordance with the existing sales name on each sheet.
That's it. Hopefully usefull.
Community Solutions Content Disclaimer
MICROSOFT CORPORATION AND/OR ITS RESPECTIVE SUPPLIERS MAKE NO REPRESENTATIONS ABOUT THE SUITABILITY, RELIABILITY, OR ACCURACY OF THE INFORMATION AND RELATED GRAPHICS CONTAINED HEREIN. ALL SUCH INFORMATION AND RELATED GRAPHICS ARE PROVIDED 'AS IS' WITHOUT WARRANTY OF ANY KIND. MICROSOFT AND/OR ITS RESPECTIVE SUPPLIERS HEREBY DISCLAIM ALL WARRANTIES AND CONDITIONS WITH REGARD TO THIS INFORMATION AND RELATED GRAPHICS, INCLUDING ALL IMPLIED WARRANTIES AND CONDITIONS OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, WORKMANLIKE EFFORT, TITLE AND NON-INFRINGEMENT. YOU SPECIFICALLY AGREE THAT IN NO EVENT SHALL MICROSOFT AND/OR ITS SUPPLIERS BE LIABLE FOR ANY DIRECT, INDIRECT, PUNITIVE, INCIDENTAL, SPECIAL, CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER INCLUDING, WITHOUT LIMITATION, DAMAGES FOR LOSS OF USE, DATA OR PROFITS, ARISING OUT OF OR IN ANY WAY CONNECTED WITH THE USE OF OR INABILITY TO USE THE INFORMATION AND RELATED GRAPHICS CONTAINED HEREIN, WHETHER BASED ON CONTRACT, TORT, NEGLIGENCE, STRICT LIABILITY OR OTHERWISE, EVEN IF MICROSOFT OR ANY OF ITS SUPPLIERS HAS BEEN ADVISED OF THE POSSIBILITY OF DAMAGES.