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

  There is a time when we must create a set of Excel Worksheets templates on a regular basis in our work. For example, a set of Excel Worksheet for each month of the year or may be based on the type of work we should done in certain time base. Here a simple example of how...

• Getting Cell Reference With Input Box

  We can get an input from user using Input Box, but we also can use the Input Box to get the address of cell reference in Microsoft Excel. Using simple Excel VBA procedure below we can ask user to select range of cells (as copy input) and where they want it paste.Sub CopyPaste()Dim InputCells As Excel.Range Dim...

• How To Hide an Excel Worksheet

  Some time we want to hide a certain Excel Worksheet from view, and it’s a common practice to use select the Worksheet, go to menu Format > Sheet > Hide. Using the method explained above is right, but unfortunately, others people can easily unhide the Worksheet using the same method, only this time, instead of selecting...

• Check If Excel Workbook is Already Open or Not

  In my case, I work a lot with transferring data from current Excel Workbook to another Excel Workbook, and to be able to do that, of course I need to make sure whether the destination Workbook is already open or not. The following excel vba function assigned to check whether a workbook we need is open...

• VBA Delete Excel Rows Based on Certain Date

  Let say I have a bunch of formatted data in my Excel sheet, and in Column A I have a dates. Then I wanna filter all the data base on certain date, for example 1 Jan 2009, and delete all others data before that date. The algorithm is like this: I’ll create a loop from the...

Valuable 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.

• Top Tips & Tricks to Get You Started in Microsoft Excel 2007

  This is an ebook by Carol Bratt's, a Microsoft Certified Professional who has plenty of experience explaining things in simple English.

• Microsoft Office 2003/XP/2007 Training Videos

  Discover an easy to use, hands-on interactive course that will teach you how to unleash the true power of Microsoft Office 2003/XP/2007.