worksheetname to be selected from a list

[DutchKevin]

Hi Guys,
I'm trying to get my new worksheet to be renamed to a value from a list of names.
It works if I put a hardcell reference there like "E7", but i'd like it to be incremented every time the macro runs. (Column E holds the names, on sheet "Total")

Sub Newsheet()
 Dim j As Long
    j = (Sheets.Count)
    Sheets.Add after:=Sheets(Sheets.Count)
    ' ActiveSheet.Name = (Sheets.Count)                         gives a simple seq number to sheet
    ' ActiveSheet.Name = Worksheets("total").Range("c1").Value  gives a defined name to sheet
    ActiveSheet.Name = Sheets("total").Range(5, j).Value
End Sub

Thanks for helping
Kevin

 

[DutchKevin]

In the meanwhile I also have tried

ActiveSheet.Name = Sheets("total").Cells(5, j).Value

But no luck
Look forward to the sollution.

Kevin

 

[DutchKevin]

Solved:
For "pulling" the name from column E with Row depending on the value of variable "j"

ActiveSheet.Name = Sheets("Total").Range("E" & j).Value

Now i still need to foolproof this to prevent the code to be activated beyond the number of entries in column E. That would lead to empty Worksheetname. Also I'll have to think about how to prevent double entries in column "E" somehow. Any tips for doing this in VBA are appreciated.

Thanks for looking


Kevin

 

[DutchKevin]

For reference to my "final" code, see post 3 in:
http://www.mrexcel.com/forum/showpost.php?p=2689708
K