Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    May 2001
    Location
    Omaha, NE USA, Nebraska, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Add a worksheet (Excel 97/SR 2)

    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>

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Add a worksheet (Excel 97/SR 2)

    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>
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Add a worksheet (Excel 97/SR 2)

    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>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    Star Lounger
    Join Date
    Jun 2001
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add a worksheet (Excel 97/SR 2)

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

  5. #5
    2 Star Lounger
    Join Date
    May 2001
    Location
    Omaha, NE USA, Nebraska, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add a worksheet (Excel 97/SR 2)

    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>

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add a worksheet (Excel 97/SR 2)

    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.

  7. #7
    2 Star Lounger
    Join Date
    May 2001
    Location
    Omaha, NE USA, Nebraska, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add a worksheet (Excel 97/SR 2)

    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>

  8. #8
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add a worksheet (Excel 97/SR 2)

    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>


  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Add a worksheet (Excel 97/SR 2)

    Very nice, Hans, it's added to my collection. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Add a worksheet (Excel 97/SR 2)

    ... 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
    -John ... I float in liquid gardens
    UTC -7ąDS

  11. #11
    2 Star Lounger
    Join Date
    May 2001
    Location
    Omaha, NE USA, Nebraska, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add a worksheet (Excel 97/SR 2)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •