Loop through Tabs

[terischmig]

I want to LOOP through all the Tabs within a worksheet to change the Tab name to the name of a specific cell. I have a Macro that will do this for one Tab at a time, but I have 100 to 200 tabs per workbook.

Thank you for your help,
Teri Schmig

[Palmetto]

Here is the basic approach, adapt as required. It assumes cell A1 on each sheet contains the value by which to name the sheet.

Option Explicit

Sub Rename_Worksheets()

    Dim ws As Worksheet
    
    Application.ScreenUpdating = False
    
    On Error Resume Next
    For Each ws In ThisWorkbook.Worksheets
        ws.Name = ws.Range("A1").Value
    Next ws
    
    Application.ScreenUpdating = True

End Sub

[shg]

Here's one way:

Sub x()
    Dim wks As Worksheet
    
    For Each wks In ActiveWorkbook.Worksheets
        With wks.Range("A1")
            If Not SheetExists(.Text, .Parent.Parent) Then
                If IsValidSheetName(.Text) Then wks.Name = .Text
            End If
        End With
    Next wks
End Sub

Function SheetExists(sWks As String, Optional wkb As Workbook) As Boolean
    On Error Resume Next
    SheetExists = Not IIf(wkb Is Nothing, ActiveWorkbook, wkb).Sheets(sWks) Is Nothing
End Function

Function IsValidSheetName(s As String) As Boolean
    If Len(s) = 0 Or Len(s) > 31 Then Exit Function
    If InStr(s, "\") Then Exit Function
    If InStr(s, "/") Then Exit Function
    If InStr(s, ":") Then Exit Function
    If InStr(s, "|") Then Exit Function
    If InStr(s, "*") Then Exit Function
    If InStr(s, "?") Then Exit Function
    IsValidSheetName = True
End Function

[terischmig]

I used Palmetto's code and it worked great - thanks!