Forum Discussion
Excluding Sheets, Help Please!
I have four sheets I want excluded from my macro (Renaming based on cell). This is my first round with macro, so I'm not sure what to input. My sheets are: Directions, Tips & Tricks, Home, Bubble Kids. And here is my macro:
Sub RenameTabs()
'Updateby20140624
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For x = 1 To Sheets.Count
If Worksheets(x).Range("B2").Value <> "" Then
Sheets(x).Name = Worksheets(x).Range("B2").Value
End If
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
Try out this code
Also I'd encourage you not to reference cells directly in your VB code (e.g. B2) and instead used Named Ranges (e.g. Go to Formula > Define Name > call it wksSheetName and set the Scope box to that sheet name). Then repeat for each sheet.
In your code you then refer to wks.Range("wksSheetName").value and your code won't break if someone inserts / deletes rows or columns moving "B2" to another cell.
Sub RenameTabs() 'Updateby20140624 Dim wks As Worksheet Application.ScreenUpdating = False Application.Calculation = xlCalculationManual For Each wks In Worksheets Select Case wks.Name Case "Directions" GoTo Skip Case "Tips & Tricks" GoTo Skip Case "Home" GoTo Skip Case "Bubble Kids" GoTo Skip Case Else If wks.Range("B2").Value <> "" Then wks.Name = wks.Range("B2").Value End If Skip: End Select Next wks Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End SubYou can allow for multiple values in case statements to simplify code:
For Each wks In Worksheets Select Case wks.Name Case "Directions", "Tips & Tricks", "Home", "Bubble Kids" GoTo Skip Case Else If wks.Range("B2").Value <> "" Then wks.Name = wks.Range("B2").Value End If End Select Skip: Next wksDefinitly use Select statements which have the same output on the same line, like Matt shows. You can use the assumed syntax, or specify it:
Case Is = Condition
Or
Case Condition
Regarding the rest of your code, there are potential pitfalls. You reference the Sheets collection, which is all worksheet types (i.e. worksheets, chart sheets, macro sheets, etc.), and then reference the Worksheets collection which is only the Worksheet type of these objects. In other words, if you have, for example, a Chart sheet in your file, you'll possibly have skewed results because the two counts/indexes do not match. If you want to reference Worksheets, use that. If you want to reference Sheets, account for that in your code (the possibility of other sheet types), but be consistent whichever you choose. I suspect you want to use the Worksheet collection, which is used 99.99% of the time.
You also don't reference a workbook. It's assumed then that it is the ActiveWorkbook. This happens because VBA is very forgiving, unlike other languages (e.g. C#). This doesn't mean you shouldn't do it, as it's good programming practices to explicitly declare all objects. I recommend declaring the workbook, even if it's the ActiveWorkbook or ThisWorkbook. Additionally, when referencing the Range object, the same principle applies. If no Range parent object (e.g. Worksheet object) is declared, the ActiveSheet is assumed. Assumptions can lead to problems down the road.
HTH
