Excel VBA Macro

Create New Excel Worksheet With VBA

Posted on the September 13th, 2008 under Workbook and Worksheet by Poer @ Excel VBA/Macro

The Excel VBA macro below will create a new Excel Worksheet called ‘RawData’ or we can use msgbox to ask for the Worksheet name if needed.

If there is already a Worksheet called RawData, user will be ask whether they want to use the old Worksheet and cancel new Worksheet creation, or delete the old Worksheet and continue creating a new blank Worksheet.

Sub CreateNewWorksheet()

    Dim oSheet As Worksheet, vRet As Variant

    On Error GoTo errHandler

    'creating a new excel worksheet called RawData
    Set oSheet = Worksheets.Add
    With oSheet
        .Name = "RawData"
        .Cells(1.1).Select
        .Activate
    End With
    Exit Sub

errHandler:

    'if error due to duplicate worksheet detected
    If Err.Number = 1004 Then
        'display an options to user
        vRet = MsgBox("Worksheet called 'RawData' is already exist, " & _
            "click yes to continue creating new Worksheet and delete the old one, " & _
            "or click no to go to the old worksheet.", _
            vbOKCancel, "Duplicate Worksheet")

        If vRet = vbOK Then
            'delete the old worksheet
            Application.DisplayAlerts = False
            Worksheets("RawData").Delete
            Application.DisplayAlerts = True

            'rename and activate the new worksheet
            With oSheet
                .Name = "RawData"
                .Cells(1.1).Select
                .Activate
            End With
        Else
            'cancel the operation, delete the new worksheet
            Application.DisplayAlerts = False
            oSheet.Delete
            Application.DisplayAlerts = True
            'activate the old worksheet
            Worksheets("RawData").Activate
        End If

    End If

End Sub

Related Entries

• Excel VBA Add Set of Worksheets Automatically
• Getting Cell Reference With Input Box
• Check If Excel Workbook is Already Open or Not
• VBA Delete Excel Rows Based on Certain Date
• How To Hide an Excel Worksheet

External Resources

• Microsoft Excel 2003/2007 Video Tutorials

  Step-by-step video guide to mastering Charts, PivotTable, Data Analysis and Macro programming in Microsoft Excel in 5 hours.

• 101 Secrets of Microsoft Excel

  Discover 101 of Excels little-known secrets that have been hiding right under your nose.

Tagged with: vba add worksheet, Worksheet