How to Create Worksheets Through Macro during run-time?
Excel Macro provides option to create worksheets dynamically during run-time. Also, users can 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.
To perform worksheet operations, we use the below functions.
[su_spoiler title=”WorkSheet Functions” open=”yes” style=”fancy” icon=”plus-square-2″]
- 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.
[su_spoiler title=”WorkSheet Properties” open=”yes” style=”fancy” icon=”plus-square-2″]
- Name – To get or change the name of a worksheet
- Count – To get number of worksheets
- Visible – To make a sheet’s visibility.
Add Sheet and 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.
Column 1 has list of Worksheet names to be created dynamically.
Column 2 to 4 has the data to be copies against the sheet name it is mapped.
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).
Visual Basic1234567891011121314151617181920212223242526272829303132333435363738394041424344Private Sub CommandButton1_Click()Add_WorkSheetsEnd SubPrivate Sub 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"'''''''''Loop to Create Worksheets DynamicallyWhile 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 "Process Completed"Sheets.VPageBreaksEnd Sub
Download Sample Excel document with Above Macro : Download 200
Additional Reference Adding Worksheets
Previous Post: Remove Junk Data From Text File
Next Post: VCF To Excel Converter – Export Contacts in vCard To Excel
Add Sheets Dynamically to Excel