Posts Tagged ‘excel vba add worksheet’

Excel VBA Add Set of Worksheets Automatically

Posted on the June 8th, 2009 under Workbook and Worksheet by Poer @ Excel VBA/Macro

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 to create a new set 12 Worksheets for each month of the year.

Sub CreateMonthlyWorksheetTemplate()
    Dim ix As Integer, oSheet As Worksheet, sMonth As String

    For ix = 1 To 12

        'add new worksheet
        Set oSheet = Worksheets.Add

        'get month name from index
        sMonth = Format("1/" & CStr(ix), "mmm")

        'rename the new worksheet
        oSheet.Name = sMonth

        Set oSheet = Nothing
    Next ix

End Sub

Read also a previous post about how vba add worksheet complete with error handler when we counter duplicate Worksheet.