How to Add Sheets in Excel – VBA Create Worksheet – Dynamic Spreadsheet

How to Create a Worksheet in Excel?

MS Office provides option to create dynamic spreadsheet in Excel during run-time.

Also, users can do to other worksheet operations like copy, move, rename and delete though VBA. Let’s begin by creating a new workbook or try downloading the Sample File at end of this topic.

If you want to learn how to do it in simple method, follow these simple steps.

Excel VBA Dynamic Spreadsheet Add – Copy Content

Here is an example, in which we have a list of worksheet names and their content present in single sheet. We are going to add new sheets as mentioned in the list and copy the content to corresponding sheet.

To begin with, create a new workbook and enter the Columns as in below image.

The above screenshot has columns as explained below.

• Column 1: list of Worksheet names to be created dynamically.
• Column 2 to 4: data to be copies against the sheet name it is mapped.

Sheets.Add After – Excel VBA Create Worksheet

Sheets.Add is the VBA command in Excel create worksheets dynamically during run-time. To perform other worksheet operations, we use the below functions.

• Add – To add a new sheet
• Copy – Copy a existing sheet
• Move – Move the sheet’s order
• Delete – Delete a sheet

Apart from these functions, below list are some of the useful properties that we use often inside Excel Macro.

• Name – To get or change the name of a worksheet
• Count – To get number of worksheets
• Visible – To make a sheet’s visibility.

To do this, copy paste the below code in Excel VB Editor and execute the code by pressing F5. (If you are new to Excel Macro, refer the Hello World program Blog in this site).

Private Sub CommandButton1_Click()
    Excel_VBA_Add_WorkSheets
End Sub

Private Sub Excel_VBA_Add_WorkSheets()
    'Declaration of Variables
    Dim iRow As Integer
    Dim Rown As Integer
    Dim TabName_Prev As String
    Dim TabName_Curr As String

    'Initialize Variables
    iRow = 1
    Rown = 1
    TabName_Prev = "Test"

    'Excel VBA Create Worksheet
    While Trim(ThisWorkbook.Sheets("Sheet1").Cells(iRow, 1)) <> ""
        iRow = iRow + 1
        TabName_Curr = Trim(ThisWorkbook.Sheets("Sheet1").Cells(iRow, 1))
        If TabName_Curr = "" Then GoTo End_Of_Process_Label

        'Create Sheet
        If TabName_Curr <> TabName_Prev Then
            ThisWorkbook.Sheets("Sheet1").Activate
            Sheets.Add after:=Sheets(Sheets.Count) 'Sheets.Add.Name = "New SheetName" will also work
            On Error Resume Next
            ActiveSheet.Name = TabName_Curr
            Rown = 1
        End If

        'Copy Data To Newly Added Sheet
        Rown = Rown + 1
        ThisWorkbook.Sheets(TabName_Curr).Cells(Rown, 1) = Trim(ThisWorkbook.Sheets("Sheet1").Cells(iRow, 2))
        ThisWorkbook.Sheets(TabName_Curr).Cells(Rown, 2) = Trim(ThisWorkbook.Sheets("Sheet1").Cells(iRow, 3))
        ThisWorkbook.Sheets(TabName_Curr).Cells(Rown, 3) = Trim(ThisWorkbook.Sheets("Sheet1").Cells(iRow, 4))
        TabName_Prev = Trim(ThisWorkbook.Sheets("Sheet1").Cells(iRow, 1))
    Wend

End_Of_Process_Label:
    MsgBox "Excel VBA - Worksheet Added Dynamically - Process Completed"
    Sheets.VPageBreaks
End Sub

Download Sample Excel document with Above Macro : Download 594

Excel VBA Adding Worksheet – Additional Reference

• http://support.microsoft.com/kb/107622
• http://stackoverflow.com/questions/3840628/creating-and-naming-worksheet-in-excel-vba