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.
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
- 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).
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.
- On the project taskpane click Sales Report.xlsx (Workbook Name), then on Insert menu select Module and write the following script:
Dim rs As Worksheet
For Each rs In Sheets
rs.Name = rs.Range("B5")
- 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.