Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Change multiple worksheet(Tab) names with Select Case statement

0

I have a workbook with 30+ tabs. Each tab name has reference to a department number in it (Ex #,003,#,#,#,# ~ where '003' is the department). I would like to create a macro to loop through all of the tabs in the workheet and depending on the Department, have it change the tab name to the name of the department. 

In the example above, the tab with '003' would be changed to 'Marketing'.

I have a list (mapping table) and thought I would probably use the Select Case to change the tab number to the Department name. (I had it working with an IF statement, but had to manually switch tabs and run macro on each tab. It's a killer to do this! Here's what the IF example lookes like:

If ActiveSheet.Name Like "*001*" Then
    ActiveSheet.Name = "Accounting"
    ElseIf ActiveSheet.Name Like "*002*" Then
    ActiveSheet.Name = "Administration"
    ElseIf ActiveSheet.Name Like "*003*" Then
    ActiveSheet.Name = "Marketing"
    ElseIf ActiveSheet.Name Like "*003*" Then 
End if
Answer
Discuss

Discussion

Queue, don't forget to select an answer for your first question also.
don (rep: 1989) Jul 8, '16 at 7:08 pm
Please don't forget to select the answer that worked best for you! Just click the Select Answer button at the bottom of the desired answer and then click Yes to verify it.
don (rep: 1989) Jul 14, '16 at 1:55 pm
Add to Discussion

Answers

0
Selected Answer

If IF statements work for you, then keep that.

You just need to loop through the sheets.

Here is a macro to do that:

Sub Sheet_Loop()

sheet_count = ActiveWorkbook.Worksheets.Count

'loop through the worksheets in the workbook

For a = 1 To sheet_count

 'activate the worksheet (this is done to work with your code)
 Sheets(a).Activate


 'Put your IF statement code here


Next a

End Sub

If you want a more thorough explanation of how to loop through worksheets, look at one of the tutorials here on teachexcel: Loop through worksheets in Excel

Discuss
0

I'm writing from my mobile so I didn't try the code so please excuse me for any errors in the code

Don answered question but if there are a lot of departments typing the if will be also a time killer especially if you have a list, so you could use index,match function for that

for example

Sub SheetNameChange()

Dim Asheet as worksheet
Dim DepN as String

'loop through the worksheets in the workbook

For each Asheet in ThisWorkbook.worksheets

'Take the N of department

 DepN = Mid (asheet.name,Instr (Asheet.name,",")+1,3)

'And change the name of that sheet to that name

Asheet.name = application.worksheetfunction.index (sheets ("list").range ("B2:B30"),application.worksheetfunction.match (DepN,sheets ("list").range ("A2:A30"),0))

Next asheet

End Sub



Note: you should change everything in bold

Discuss

Discussion

Having trouble with above answer. 
I have created a worksheet, named 'list' and on it I have the Dept# in column A (it's actually a String, I changed the Dim DepN as Integer to String; and in column B I have the corresponding Department Name.  I did revise the items in Green and BOLD. ~ I'm getting 'Object required' error.
I believe I understand the 'lookup' by using Index & match.....
  scratch that, I just found issue....the formula for "DepN" has typo in 'Ashwet' (s/b Asheet!) until I manually typed in, i didn't see it either!  (I shouldn't be so lazy to cut & past code!!    Great solution though and does eliminate all that typing in the Code.  Tremendous! thank you
queue (rep: 467) Jul 14, '16 at 2:39 pm
Welcome!!
I will edit the string and the typo in Ashwet.
MRVMV (rep: 52) Jul 14, '16 at 2:49 pm
Please don't forget to select the answer that worked best for you! Just click the Select Answer button at the bottom of the desired answer and then click Yes to verify it.
MRVMV (rep: 52) Jul 14, '16 at 2:54 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login