rename the new worksheet

G

Guest

i kindly ask for help.
in sheet1 i have a list, i need to create a new worksheet and name it with
the value of cell which i selected in this list. i tried something like this,
but it´s surely very simple

Sub Create()
Dim ws As Worksheet
Dim wsName As String
If Not IsEmpty(ActiveSheet.ActiveCell.Value) Then
Set ws = Worksheets.Add
ws.Move After:=Sheets(Sheets.Count)
ws.Name = ActiveSheet.ActiveCell.Text
End If
End Sub
 
G

Guest

Sub Create()
Dim ws As Worksheet
Dim wsName As String
If Not IsEmpty(ActiveCell) Then
newname = ActiveCell.Value
Set ws = Worksheets.Add
ws.Move After:=Sheets(Sheets.Count)
ws.Name = newname
End If
End Sub

you needed a little fix to the IsEmpty. You also need to capture the tab
name before the .Add

After the .Add the ActiveSheet becomes the new sheet!! Which is, of course,
totally empty.
 
B

Bob Phillips

Sub Create()
Dim cell As Range

For Each cell In Selection
If Not IsEmpty(cell) Then
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name =
cell.Value
End If
Next cell

End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
I

IanKR

Sub Create()
Dim ws As Worksheet
Dim wsName As String
If Not IsEmpty(ActiveCell) Then
newname = ActiveCell.Value
Set ws = Worksheets.Add
ws.Move After:=Sheets(Sheets.Count)
ws.Name = newname
End If
End Sub

you needed a little fix to the IsEmpty. You also need to capture the
tab name before the .Add

After the .Add the ActiveSheet becomes the new sheet!! Which is, of
course, totally empty.

Is it not also a good idea to test that there isn't already a worksheet of
the intended name before you name the new one?

Before ws.Name = newname

put:

Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
If wks.Name = newname Then
<ask user how to proceed via msgbox - e.g. delete the exitsting one,
etc>
End If
Next wks
 
G

Guest

Yes it is a good idea. If the name already exists, an error will be thrown
and the new sheet will be left with its birthname - like Sheet8
 
G

Guest

Thanks a lot. I tried to implement also the test of existing ws, but it
didn´t work well.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top