Creating and Renaming New worksheets?

Hi everyone,

I have a column of names from B8 to B52 in a worksheet called "MasterList", I would like a macro that helps me create 44 new worksheets and Name them (1 for each of the values in the cells B8, B9, B10 etc.
Eg. If B8 has the value Apple, B9 has the value Orange, I would like the new sheet to be name Apple, the second new worksheet to be named Orange and so forth..

Any help would be greatly appreciated!

Thank you

tonylyx,

Give this a try:

Sub tgr()
    
    Dim ws As Worksheet
    Dim rngNames As Range
    Dim NameCell As Range
    Dim strName As String
    Dim i As Long
    
    Set ws = Sheets("MasterList")
    Set rngNames = ws.Range("B8", ws.Cells(Rows.Count, "B").End(xlUp))
    If rngNames.Row < 8 Then Exit Sub   'No data
    
    For Each NameCell In rngNames.Cells
        strName = NameCell.Text
        
        'Ensure that the name is a legal worksheet name
        For i = 1 To 7
            strName = Replace(strName, Mid(":\/?*[]", i, 1), " ")
        Next i
        strName = Trim(Left(WorksheetFunction.Trim(strName), 31))
        
        'Check if a sheet already exists with that name
        If Evaluate("IsRef('" & strName & "'!A1)") = False Then
            'Doesn't already exist, make a new sheet with that name
            Sheets.Add After:=Sheets(Sheets.Count)
            ActiveSheet.Name = strName
        End If
    Next NameCell
    
    Set ws = Nothing
    Set rngNames = Nothing
    Set NameCell = Nothing
    
End Sub

Select the MasterList worksheet and run this small macro:

Sub SheetAdder()
    Dim s As Worksheet
    Set s = ActiveSheet
    For i = 8 To 52
        Sheets.Add
        ActiveSheet.Name = s.Cells(i, "B").Value
    Next
End Sub