How to rename sheets in a workbook with exceptions?

Question

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

Damian · Accepted Answer · 2020-06-16T09:12:53.073

2

Your code had 3 mistakes

You were using Activate and Active which produced the second error. Read this on how to avoid these.
You were checking the name of the ActiveSheet before the ws.Activate so it would always check the previous sheet.
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

edited Jun 16 '20 at 09:12

answered Jun 16 '20 at 08:55

Damian

5,152
1
10
21

Thanks for the answer @Damian, your code gives me a compile error "End if without Block IF" – Yusuf Jun 16 '20 at 09:03
@Yusuf yeah, noticed and fixed it a minute ago. Try the new code on my answer. – Damian Jun 16 '20 at 09:04
There's another issue, the code is attempting to rename all the sheets using the "G2" value from the Activesheet . can you see this? – Yusuf Jun 16 '20 at 09:10
@Yusuf You are right, sorry. Missed the `.` in front of `Range("G2")` for the `Else` just add the dot there like in my answer and it will work fine. – Damian Jun 16 '20 at 09:13
Its fixed by declaring the sheetname : .Name = ws.Range("G2").End(xlDown) Else .Name = ws.Range("G2") – Yusuf Jun 16 '20 at 09:17
@Yusuf you don't need the whole reference, just the `.` in front of the range because it's withing a `With` block which means everything followed by the `.` will reference the `ws`. If my answer helped, please consider marking it as correct to close it. – Damian Jun 16 '20 at 09:19

How to rename sheets in a workbook with exceptions?

1 Answers1