CODEWITHSUNDEEP
excelvba
Yusuf
Yusuf

Reputation: 31

How to rename sheets in a workbook with exceptions?

I intend to rename all the sheets with the cell "G2" value except the two sheets called "Main" and "Fixed".

The code keeps renaming the two sheets.

Sub RenameSheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    'With ActiveWorkbook.ActiveSheet
    If ActiveWorkbook.ActiveSheet.Name <> "Main" Or _
      ActiveWorkbook.ActiveSheet.Name <> "Fixed" Then

        ws.Activate
        If Range("G2").Value = "" Then
            ActiveSheet.Name = Range("G2").End(xlDown).Value
        Else: ActiveSheet.Name = Range("G2").Value
        End If

    Else:
        GoTo Nextsv

End If

Nextsv:     Next ws

Sheets("Main").Activate
ActiveSheet.Cells(1, 1).Select
End Sub

Upvotes: 0

Views: 205

Answers (1)

Damian
Damian

Reputation: 5174

Your code had 3 mistakes

  1. You were using Activate and Active which produced the second error. Read this on how to avoid these.
  2. You were checking the name of the ActiveSheet before the ws.Activate so it would always check the previous sheet.
  3. Your conditions were with Or. Main <> Fixed so it would change their name anyways because Main is not Fixed and mets the second part of your Or and viceversa. Use And to force the code check that both conditions are met.

This is the code without using Activate or Select:

Option Explicit
Sub RenameSheets()

    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> "Main" And ws.Name <> "Fixed" Then
            With ws
                If .Range("G2") = vbNullString Then
                    .Name = .Range("G2").End(xlDown)
                Else
                    .Name = .Range("G2")
                End If
            End With
        End If
    Next ws

End Sub

Upvotes: 2

Related Questions