Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Aug 2005
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Adding sheets (Excel 2003)

    Hello Everyone,

    I am trying to add a VB command to add a sheet in a workbook. I also need to populate the sheet with some info. I tried the following code, but it does not work. Any help would be great.

    Sub InsertSheet ()
    Sheets.Add
    Sheets("Sheet6").Select
    Sheets("Sheet6").Name = "Allotment"
    Range("A4").Select
    ActiveCell.FormulaR1C1 = "Date:"
    Range("A5").Select
    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adding sheets (Excel 2003)

    When you run the macro, the new sheet isn't necessarily named Sheet6. Try this:

    Sub InsertSheet()
    With WorkSheets.Add
    .Name = "Allotment"
    .Range("A4") = "Date:"
    .Range("A5").Select
    End With
    End Sub

  3. #3
    Lounger
    Join Date
    Aug 2005
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding sheets (Excel 2003)

    Thanks HansV. That work, but now one more question. If I tried to run the macro again, I get an error. What other VB command do I need to include so in case someone runs the macro again it will not give them an error? Thanks.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adding sheets (Excel 2003)

    You can have only one worksheet with a given name - "Allotment" in this case. So if you want the macro to be run several times, you must either omit naming the new worksheet (Excel will automatically give it a unique name), or devise a scheme to assign a unique name in code.

  5. #5
    Lounger
    Join Date
    Aug 2005
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding sheets (Excel 2003)

    What about doing a loop? I only want to create this worksheet one time, but I am afraid that the people using the spreadsheet may run the macro by mistake in which case I don't want them to get an error message. For example if they press the macro. The VB will check to see if the "Allotment" worksheet is present if not then it will create it with all the other information on the macro.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adding sheets (Excel 2003)

    You can test like this:

    Sub InsertSheet()
    Dim wsh As Worksheet
    On Error Resume Next
    Set wsh = Worksheets("Allotment")
    ' If no error occurred, the worksheet already exists
    If Err = 0 Then Exit Sub
    On Error GoTo 0
    With WorkSheets.Add
    .Name = "Allotment"
    .Range("A4") = "Date:"
    .Range("A5").Select
    End With
    End Sub

  7. #7
    Lounger
    Join Date
    Aug 2005
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding sheets (Excel 2003)

    Thanks Hans for all your help.

Posting Permissions

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