Excel 2010 Rename sheets if not called Summary

Could someone provide me with VBA code to rename sheets Sheet1, Sheet2 etc that are not already named Summary

Thanks
JagwarmanAsked:
Who is Participating?
 
Rgonzo1971Connect With a Mentor Commented:
pls try
Sub macro()
ShIndex = 0
    For Each sh In ActiveWorkbook.Worksheets
        If sh.Name <> "Summary" And Not (sh.Name Like "Sheet[1-9]") And Not (sh.Name Like "Sheet[1-9][0-9]") Then
        Do
            Set TestSheet = Nothing
            ShIndex = ShIndex + 1
            On Error Resume Next
            Set TestSheet = Sheets("Sheet" & ShIndex)
            On Error GoTo 0
        Loop While Not (TestSheet Is Nothing)
        sh.Name = "Sheet" & ShIndex
        ShIndex = 0
        End If
    Next
End Sub 

Open in new window

0
 
SreeramConnect With a Mentor Commented:
HI Jagwarman,

Try this:

Sub Macro1()
    Dim I As Integer
        For I = 1 To Application.ActiveWorkbook.Worksheets.Count
            If Application.ActiveWorkbook.Sheets(I).Name <> "Summary" Then
                Application.ActiveWorkbook.Sheets(I).Name = "Give_Your_Sheet_Name"
            End If
        Next
           
End Sub
0
 
NorieVBA ExpertCommented:
Rename them to what?
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Rgonzo1971Commented:
HI,

This code preserves the sheets named Sheet1, Sheet2, etc

Sub macro()
ShIndex = 0
    For Each sh In ThisWorkbook.Worksheets
        If sh.Name <> "Summary" And Not (sh.Name Like "Sheet[1-9]") And Not (sh.Name Like "Sheet[1-9][0-9]") Then
        Do
            Set TestSheet = Nothing
            ShIndex = ShIndex + 1
            On Error Resume Next
            Set TestSheet = Sheets("Sheet" & ShIndex)
            On Error GoTo 0
        Loop While Not (TestSheet Is Nothing)
        sh.Name = "Sheet" & ShIndex
        ShIndex = 0
        End If
    Next
End Sub

Open in new window

Regards
0
 
JagwarmanAuthor Commented:
it must be me because I cannot get this to work
0
 
JagwarmanAuthor Commented:
Ah this has to be in the Workbook that I have opened to work.
0
 
JagwarmanAuthor Commented:
Thanks
0
All Courses

From novice to tech pro — start learning today.