Rename the worksheet based on cell value

[mayaa_mmm]

I want to rename the worksheet based on cell value and delete the row where cell value
1.Worksheet 1 contains cell value A1 have fruits. Worksheet 1 will be renamed as fruits. In the worksheet fruits, Cell value row should get delete. Worksheet 2 contains cell value A1 have vegetables. Worksheet 2 will be renamed as vegetables, cell value row will be deleted.
Some cases, Worksheet 2 can have cell value fruits,Worksheet 1 can have cell value vegetables
2. Sheet order may vary. Code should work where the word matches in the entire workbook, It should rename the worksheet and delete the cel value row
3. automatically rename till the last value

 

[Sharid]

See this video See Youtube video

Code is in the description

 

[VBE313]

Sub WorksheetLoop()
Dim worksheetsCount As Integer, I As Integer, sheetName As String
worksheetsCount = ActiveWorkbook.worksheets.Count
    For I = 1 To worksheetsCount
        sheetName = ActiveWorkbook.worksheets(I).Name
        If Sheets(sheetName).Range("A1").Value = "fruits" Or _
         Sheets(sheetName).Range("A1").Value = "Fruits" Or _
         Sheets(sheetName).Range("A1").Value = "Vegetables" Or _
         Sheets(sheetName).Range("A1").Value = "vegetables" Then
                Sheets(sheetName).Name = Sheets(sheetName).Range("A1")
                Sheets(sheetName).Range("A1").EntireRow.Delete
        End If
    Next I
End Sub

 

[mayaa_mmm]

Sub WorksheetLoop()
Dim worksheetsCount As Integer, I As Integer, sheetName As String
worksheetsCount = ActiveWorkbook.worksheets.Count
    For I = 1 To worksheetsCount
        sheetName = ActiveWorkbook.worksheets(I).Name
        If Sheets(sheetName).Range("A1").Value = "fruits" Or _
         Sheets(sheetName).Range("A1").Value = "Fruits" Or _
         Sheets(sheetName).Range("A1").Value = "Vegetables" Or _
         Sheets(sheetName).Range("A1").Value = "vegetables" Then
                Sheets(sheetName).Name = Sheets(sheetName).Range("A1")
                Sheets(sheetName).Range("A1").EntireRow.Delete
        End If
    Next I
End Sub

It throws error in the Sheets(sheetName).Range("A1").EntireRow.Delete
Not getting deleted and not moving to next sheet for renaming

 

[VBE313]

How many tabs are in your workbook? Keep in mind that only one sheet can be named fruits, and only one sheet can be named vegetables.

Sub WorksheetLoop()
Dim worksheetsCount As Integer, I As Integer, sheetName As String
worksheetsCount = ActiveWorkbook.Worksheets.Count
    For I = 1 To worksheetsCount
        sheetName = ActiveWorkbook.Worksheets(I).Name
        If Sheets(sheetName).Range("A1").Value = "fruits" Or _
         Sheets(sheetName).Range("A1").Value = "Fruits" Or _
         Sheets(sheetName).Range("A1").Value = "Vegetables" Or _
         Sheets(sheetName).Range("A1").Value = "vegetables" Then
                Sheets(sheetName).Name = Sheets(sheetName).Range("A1")
                sheetName = ActiveWorkbook.Worksheets(I).Name
                Sheets(sheetName).Range("A1").EntireRow.Delete
        End If
    Next I
End Sub

 

[mayaa_mmm]

How many tabs are in your workbook? Keep in mind that only one sheet can be named fruits, and only one sheet can be named vegetables.

Sub WorksheetLoop()
Dim worksheetsCount As Integer, I As Integer, sheetName As String
worksheetsCount = ActiveWorkbook.Worksheets.Count
    For I = 1 To worksheetsCount
        sheetName = ActiveWorkbook.Worksheets(I).Name
        If Sheets(sheetName).Range("A1").Value = "fruits" Or _
         Sheets(sheetName).Range("A1").Value = "Fruits" Or _
         Sheets(sheetName).Range("A1").Value = "Vegetables" Or _
         Sheets(sheetName).Range("A1").Value = "vegetables" Then
                Sheets(sheetName).Name = Sheets(sheetName).Range("A1")
                sheetName = ActiveWorkbook.Worksheets(I).Name
                Sheets(sheetName).Range("A1").EntireRow.Delete
        End If
    Next I
End Sub

I have 8 worksheets but cell value is different in 8 worksheets, but it is moving to another sheet automaitcally

 

[VBE313]

It will move to another sheet if the A1 value doesnt = "Fruits", "fruits", "vegetables", or "Vegetables". Please values are allowed to change the sheet name.