Rename sheet without Activating it

[cgclower]

Hello!

I am trying to write a macro to loop through all the sheets in a workbook, and if a cell is a certain value, rename the worksheet. Here is what I have so far:

For Each Sheet In ActiveWorkbook.Sheets
Sheet.Activate
If Sheet.Range("B1").Value = "Rename me" Then
ActiveSheet.Name = "NewName"
End If
Next Sheet

My question is, is there a way to accomplish this without having to activate every sheet every time?

Thanks for the help!

 

[pgc01]

Hi

Remark:
In your code you are renaming the worksheet to the constant "NewName". This means that you can only rename 1 worksheet as you cannot have 2 worksheets with the same name.


Try:

Sub RenWks()
Dim wks As Worksheet
 
For Each wks In ActiveWorkbook.Worksheets
    With wks
        If .Range("B1").Value = "Rename me" Then wks.Name = "NewName"
    End With
Next wks
End Sub