Add a worksheet (Excel 97/SR 2)

[steve69]

Is it possible to add a worksheet to the active workbook with a preset name? Sound confusing <img src=/S/confused.gif border=0 alt=confused width=15 height=20>, that's because I suck at explaining things.

Basically, I am looking for a way to add a worksheet and name it "Average" (without quotes). I know how to do this if I know the name of the sheet that was just added. For example, if I open a new workbook with 3 worksheets (sheet1, sheet2, and sheet3) I know how to change the name of the new worksheet (sheet4) to Average. But what if I don't know how many worksheets are in the workbook and/or don't know what their names are.

Is it possible to do something like Worksheet("Average").Add? <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15>

[JohnBF]

A new sheet added to the active workbook is the active sheet when it's added, so

ThisWorkbook.Sheets.Add
ActiveSheet.Name = "Average"

works without you knowing which name/code it is and without knowing the name/codes of the existing sheets. Or more likely I've completely missed the point of your question. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

[SammyB]

Steve, I think that what you are missing is that all of the add methods return an object, so you can either dim this object or use a with block. In your case, you can<pre>Dim w As Worksheet
Set w = Worksheets.Add
w.Name = "Average"</pre>

or if you wat to do it more elegantly<pre> With Worksheets.Add
.Name = "Slicker"
End With</pre>

[BobUmlas]

How about:
Worksheets.Add.Name="Average"
???

[steve69]

Bob, Sammy & John,

<img src=/S/fanfare.gif border=0 alt=fanfare width=31 height=23> for your solutions, I tried several of them and they worked great. <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

<img src=/S/moon.gif border=0 alt=moon width=15 height=15> BUT, now I have another question.

If the worksheet already has a worksheet named 'Average' I know I can do an if statement and then Sheets("Average").Delete. Any idea how to get rid of the dialog box that warns about deleting a worksheet is a permanent action. Any way to automatically pass <enter> to this dialog box?

<img src=/S/grovel.gif border=0 alt=grovel width=31 height=23> <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23> <img src=/S/headthrob.gif border=0 alt=headthrob width=15 height=15>

[Hans Pottel]

You can use something like:

<pre>Sub test()
On Error Resume Next
Application.DisplayAlerts = False
Sheets("Average").Delete
Application.DisplayAlerts = True
On Error GoTo 0
End Sub
</pre>


the 'on error resume next' is necessary in case there is no "Average" sheet to delete. The DisplayAlerts property of the application object can be switched true/false to prevent Excel's messages to appear.

[steve69]

Thanks Hans - worked like a charm. <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30> <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> <img src=/S/fanfare.gif border=0 alt=fanfare width=31 height=23> <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21> <img src=/S/salute.gif border=0 alt=salute width=15 height=20> <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

[Hans Pottel]

FYI: An alternative might be adding a suffix to the name of the sheet: in that case you can make many different 'Average' sheets. I once wrote a macro for something equivalent, here is the code

<pre>Sub AddReportSheet()
Dim Sh As Worksheet
Dim BaseReportName As String
Dim MyIndex As Integer
BaseReportName = "Report"
Set Sh = Worksheets.Add
MyIndex = 1
On Error Resume Next
Sh.Name = BaseReportName & MyIndex
Do Until Err.Number = 0
Err.Clear
MyIndex = MyIndex + 1
Sh.Name = BaseReportName & MyIndex
Loop
End Sub
</pre>

[JohnBF]

Very nice, Hans, it's added to my collection. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

[JohnBF]

... because I wanted to put this all together and learn how to handle without killing the existing sheet if it exists, conceptual credit goes to Legare for an old post of his, and to everyone above, abuse for misusing his code should be directed to me, improvements welcome:

Sub AddAverageSheet()
Dim oSheet As Worksheet
On Error Resume Next
Set oSheet = Worksheets("Average")
On Error GoTo 0
If oSheet Is Nothing Then
ThisWorkbook.Sheets.Add.Name = "Average"
Set oSheet = Worksheets("Average") '<oops, edited>
Else
MsgBox "A Sheet named Average already exists."
End If
End Sub

[steve69]

John and Hans, great job. <img src=/S/salute.gif border=0 alt=salute width=15 height=20> I will save for future reference.

Just as an FYI, in this case, I "know" that the averages worksheet already exists, but I wanted to delete it and start over from scratch. With everyone's help I have been able to accomplish what I needed very simply.

Thanks.