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).
12345678910111213141516171819202122232425262728293031323334353637383940414243Private Sub CommandButton1_Click()Excel_VBA_Add_WorkSheetsEnd SubPrivate Sub Excel_VBA_Add_WorkSheets()'Declaration of VariablesDim iRow As IntegerDim Rown As IntegerDim TabName_Prev As StringDim TabName_Curr As String'Initialize VariablesiRow = 1Rown = 1TabName_Prev = "Test"'Excel VBA Create WorksheetWhile Trim(ThisWorkbook.Sheets("Sheet1").Cells(iRow, 1)) <> ""iRow = iRow + 1TabName_Curr = Trim(ThisWorkbook.Sheets("Sheet1").Cells(iRow, 1))If TabName_Curr = "" Then GoTo End_Of_Process_Label'Create SheetIf TabName_Curr <> TabName_Prev ThenThisWorkbook.Sheets("Sheet1").ActivateSheets.Add after:=Sheets(Sheets.Count) 'Sheets.Add.Name = "New SheetName" will also workOn Error Resume NextActiveSheet.Name = TabName_CurrRown = 1End If'Copy Data To Newly Added SheetRown = Rown + 1ThisWorkbook.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))WendEnd_Of_Process_Label:MsgBox "Excel VBA - Worksheet Added Dynamically - Process Completed"Sheets.VPageBreaksEnd Sub
Download Sample Excel document with Above Macro : Download 236
Excel VBA Adding Worksheet – Additional Reference
How to Add Sheets in Excel – VBA Create Worksheet – Dynamic Spreadsheet