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