Naming and Adding Worksheets

[Guest]

I want to Name the active worksheet, then add a new worksheet and name that
worksheet. How would I do that?

Thanks

Brenda

 

[Guest]

I want to add the new worksheet after the current active worksheet. I would
obvioulsy know the name of the current active worksheet.

Brenda

 

[Peter T]

Hi Brenda,

Sub test()
Dim ws As Worksheet, sName As String
sName = "MySheet"

Set ws = Worksheets.Add(after:=ActiveSheet)
On Error Resume Next
ws.Name = sName
If Err.Number = 1004 Then
MsgBox "Cannot rename: " & ws.Name & vbCr & _
sName & " is already in use"
End If
On Error GoTo 0
End Sub

Here "MySheet" is hardcoded, but you may want some other way of giving the
name, an InputBox perhaps.

Regards,
Peter T