names to worksheets from selected cells

[Roland Hall]

Hello everyone,
Can anybody tell me the formula (VBA) to create new worksheets and name them from a list already on a
worksheet...
eg...I have a list of student names that I want to automatically create a series of worksheets with their names on them.
I have tried to do this with a macro, but it does not work.
Please help

 

[Andrew Poulsom]

Welcome to MrExcel.

What have you tried that didn't work?

 

[Roland Hall]

Thank you for replying. I managed to find a VBA formula that worked.
I do have one question-:
How do you combine 2 cells together...for eg a name and surname so that they can be used in a VBA statement like the following-

Sub Addsheets()
Dim LR As Long, i As Long
With Sheets("Sheet2")
LR = .Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = .Range("A" & i).Value
Next i
End With
End Sub


eg... on the active sheet there will be a First Name and a Surname. I want to combine these two, create a space in between(if it is possible) and then use the formula above (except with an active worksheet instead of "Sheet2") to create a whole lot of new worksheets with names on them.

 

[Andrew Poulsom]

Like this?

Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = .Range("A" & i).Value & " " & .Range("B" & i).Value

 

[Roland Hall]

I couldnt get that to work. How do you do it...do you put it inside the formula? Do you put it directly in a cell?
How does this work?

 

[Andrew Poulsom]

In what way didn't it work?