• Adding sheets (Excel 2003)

    Author
    Topic
    #423107

    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

    Viewing 0 reply threads
    Author
    Replies
    • #967109

      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

      • #967112

        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.

        • #967115

          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.

          • #967118

            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.

            • #967121

              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

            • #967134

              Thanks Hans for all your help.

    Viewing 0 reply threads
    Reply To: Adding sheets (Excel 2003)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: