Renaming Worksheets with For Each Loop

[lance-news]

Hello

I am trying to loop through the Worksheets and rename the various
worksheets based on the name in Cell A1. The below script will rename
the first sheet "Means" but then I get an error
"Cannot rename a sheet to the same name as another sheet"

What is wrong with my script?



Sub A0Rename()

For Each sh In Worksheets

If ActiveSheet.Cells(1, 1).Value = "Descriptives" Then sh.Name = "Means"
If ActiveSheet.Cells(1, 1).Value = "ANOVA" Then sh.Name = "ANOVA"

If ActiveSheet.Cells(1, 1).Value = "Test of Homogeneity of Variances"_
Then sh.Name = "HOV"

If ActiveSheet.Cells(1, 1).Value = "Multiple Comparisons" Then sh.Name_
= "Pairwise"

Next sh

 

[Trevor Shuttleworth]

You're always looking at cell A1 so that means you try to rename every sheet
with whatever is in that cell. The first sheet gets renamed, the second
sheet stops the code.

Regards

Trevor

 

[Tom Ogilvy]

You were always looking at the activesheet to get the value of A1 (so the
value never changed) - while looping through the sheet, so on the second
sheet, you tried to rename it the same name as the first sheet.

Sub A0Rename()

For Each sh In Worksheets

If sh.Cells(1, 1).Value = "Descriptives" Then sh.Name = "Means"
If sh.Cells(1, 1).Value = "ANOVA" Then sh.Name = "ANOVA"

If sh.Cells(1, 1).Value = "Test of Homogeneity of Variances"_
Then sh.Name = "HOV"

If sh.Cells(1, 1).Value = "Multiple Comparisons" Then sh.Name_
= "Pairwise"

Next sh

End Sub

 

[rene]

If u want to rename a sheet based on the name of the A1 cell of that sheet u
need to write something like this :

Sub A0Rename()

For Each sh In Worksheets
If sh.Cells(1, 1).Value = "Descriptives" Then sh.Name = "Means"
If sh.Cells(1, 1).Value = "ANOVA" Then sh.Name = "ANOVA"

If sh.Cells(1, 1).Value = "Test of Homogeneity of Variances"_
Then sh.Name = "HOV"
If sh.Cells(1, 1).Value = "Multiple Comparisons" Then sh.Name_
= "Pairwise"

Next sh

Truly yours,

René.