Select the product you need help with

Step by Step : Rename Excel Sheet with Cell Contents by Using Macro

Article ID: 2790229 - View products that this article applies to.

About Author:

Collapse this tableExpand this table

Collapse this imageExpand this image

This article is provided by MVP Raddini Rahayu

(http://mvp.microsoft.com/en-US/findanmvp/Pages/profile.aspx?MVPID=ca248a8b-f840-424d-9302-1c100704987e)

. Microsoft is so thankful that MVPs who proactively share their professional experience with other users. The article would be posted on MVP's website

(http://mvp.microsoft.com/en-US/findanmvp/Pages/profile.aspx?MVPID=ca248a8b-f840-424d-9302-1c100704987e)

or blog

(http://excelmiso.blogspot.com)

later.

Expand all | Collapse all

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.

Collapse this imageExpand this image

Back to the top | Give Feedback

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.

Back to the top | Give Feedback

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).

Collapse this imageExpand this image

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+F11 on the keyboard, so Visual Basic window is displayed.

Collapse this imageExpand this image
On the project taskpane click Sales Report.xlsx (Workbook Name), then on Insert menu select Module and write the following script:
Sub RenameSheet() Dim rs As Worksheet For Each rs In Sheets rs.Name = rs.Range("B5") Next rs End Sub
([anySimpleType])
Collapse this imageExpand this image
Press the F5 key 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.

Collapse this imageExpand this image

That's it. Hopefully usefull.

Back to the top | Give Feedback

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.

Back to the top | Give Feedback

Note This is a "FAST PUBLISH" article created directly from within the Microsoft support organization. The information contained herein is provided as-is in response to emerging issues. As a result of the speed in making it available, the materials may include typographical errors and may be revised at any time without notice. See Terms of Use

(http://go.microsoft.com/fwlink/?LinkId=151500)

for other considerations.

Back to the top | Give Feedback