VB Code to rename sheet

[backfromthebush]

Hi,
Im having a problem with my code in trying to rename a new sheet.

The first sheet is named "Data", and in column B I have a list of names which I eventually want each one to have a new sheet named after.

Lets say B1 has the name "Apples", I need the code to create a new tab called "Apples", then go to cell B2, which will have "Bananas", and create a new tab called "Bananas", etc, until it reaches a cell with no data, where it will exit the procedure.

Can anyone please help!!

 

[Blade Hunter]

Dump the list to an array
Poll the array and for each entry that is not blank, create a new sheet and name accordingly

Sub NewSheetsFromList()
Dim MyList() As Variant
Dim MyVal As String
MyList = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
For Each MyVal In MyList
    If MyVal <> "" Then
        Worksheets.Add
        ActiveSheet.Name = MyVal
    End If
Next
Sheets("Data").select
End Sub

 

[Dryver14]

Blade,

If the list is alphabetical is it not worth using

Worksheets.Add after:=Worksheets(Worksheets.Count)

Or does your method follow on anyway?

 

[Blade Hunter]

It just adds them to the start in the order they were listed. (Which will effectively reverse the list. Making the change you suggest would be a good idea to maintain structure.