Excel VBA Macro

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.

Related Entries

• Create New Excel Worksheet With VBA
• Sum Unique/Distinct Values in Excel
• Excel VBA Looping Tutorial
• Find the Last Day of any Month
• Auto Format Excel Cells with Error Value

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: excel vba add worksheet, excel vba worksheet, excel worksheet