Excel Tool

Order Online
Our company contact information There you can find frequently asked questions and answers

Home | Products |Purchase | FAQ | Contact Us | Useful Resources

Automatically Insert Worksheet in Excel

Question 1:

Automatically Insert Worksheet in Excel

If we want to enter text (such as 101, 102, ...... etc.) in the table after, Excel will automatically insert a worksheet, and named after the cell contents, you can do this:

VBA is easy, Right click on the sheet tab, select the "view code" and copy and paste the following code at the cursor:

Private Sub Worksheet_Change(ByVal Target As Range)
'If you encounter a program error, without interruption, to continue down (when the original worksheet the same name exists, renamed step to be wrong)
On Error Resume Next

'If the value of more than one cell changes, exit the program (do not insert a worksheet)
If Target.Count > 1 Then Exit Sub

If Intersect(Target, Range("B5:G10")) Is Nothing Then Exit Sub 'If the selected cell does not B5: in the G10 area, exit the program

If Target = "" Then Exit Sub 'If you clear cell values, exit the program

If MsgBox(Target.Value & " worksheet to insert it? ", 36, "Prompt") = 7 Then Exit Sub 'Prompt, if not prompted by deleting this line of code

Sheets.Add 'Insert a worksheet
ActiveSheet.Name = CStr(Target.Value) 'To rename a worksheet (the name for the content of the active worksheet)
ActiveSheet.Move after:=Sheets(Sheets.Count) 'The new worksheet to the last

End Sub

Example File: data sheet1.xlsm


Question 2:

Actual use, we hope to click on a cell, automatically activate the corresponding worksheet, and when you enter text, insert a new worksheet.

To achieve this function, simply write two pieces of code in the "Directory" modules:

Automatically Insert Worksheet in Excel

'Code to activate the worksheet
Private Sub Worksheet_SelectionChange(ByVal Target As Range)


'If you choose an Excel range, do not run the following code, exit the program
If Target.Count > 1 Then Exit Sub


'If the cell is not selected within the B5:G10 exit the program
If Intersect(Target, Range("B5:G10")) Is Nothing Then Exit Sub
If Target = "" Then Exit Sub 'If the cell is empty, exit the program

Sheets(CStr(Target.Value)).Visible = 1 'Unhide, display the worksheet
Sheets(CStr(Target.Value)).Activate 'Activate the worksheet the same name

End Sub


'Insert Sheet code
Private Sub Worksheet_Change(ByVal Target As Range)
'If you encounter a program error, without interruption, to continue down (when the original worksheet the same name exists, renamed step to be wrong)
On Error Resume Next

'If the value of more than one cell changes, exit the program (do not insert a worksheet)
If Target.Count > 1 Then Exit Sub

If Intersect(Target, Range("B5:G10")) Is Nothing Then Exit Sub 'If the selected cell does not B5: in the G10 area, exit the program

If Target = "" Then Exit Sub 'If you clear cell values, exit the program

If MsgBox(Target.Value & " worksheet to insert it? ", 36, "Prompt") = 7 Then Exit Sub 'Prompt, if not prompted by deleting this line of code

Sheets.Add 'Insert a worksheet
ActiveSheet.Name = CStr(Target.Value) 'To rename a worksheet (the name for the content of the active worksheet)
ActiveSheet.Move after:=Sheets(Sheets.Count) 'The new worksheet to the last

End Sub

Two pieces of code no order of the points, which is the former, which does not matter in the post.

Add hidden worksheet code in "ThisWorkbook" module

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)

If Sh.Name <> "Directory" Then Sh.Visible = 2 'If the sheet name is not a "directory", then hide it

End Sub

Example File: data sheet2.xlsm


Question 3:

Actual use, we hope not to insert a blank worksheet, but as they are now seeing this, a unified table style:

You can do this:
1, only one worksheet to create a workbook, good design table style, when you save the workbook, select Save as type is "Excel Template(*.xltx)"

2. Right-click on this worksheet tab, select "View Code" and paste the following code to the cursor, check the template where the path is correct:

Private Sub Worksheet_Change(ByVal Target As Range)
'If you encounter a program error, without interruption, to continue down (when the original worksheet the same name exists, renamed step to be wrong)
On Error Resume Next

'If the value of more than one cell changes, exit the program (do not insert a worksheet)
If Target.Count > 1 Then Exit Sub

If Intersect(Target, Range("B5:G10")) Is Nothing Then Exit Sub 'If the selected cell does not B5: in the G10 area, exit the program

If Target = "" Then Exit Sub 'If you clear cell values, exit the program

If MsgBox(Target.Value & " worksheet to insert it? ", 36, "Prompt") = 7 Then Exit Sub 'Prompt, if not prompted by deleting this line of code

Sheets.Add Type:=ThisWorkbook.Path & "\mytemp.xltx" 'Insert Sheet (using templates mytemp.xltx)

ActiveSheet.Name = CStr(Target.Value) 'To rename a worksheet (the name for the content of the active worksheet)
ActiveSheet.Move after:=Sheets(Sheets.Count) 'The new worksheet to the last

End Sub

Compared with the previous code, only one line is not the same, is to insert a worksheet based on a template.
In "Sheets.Add Type: = ThisWorkbook.Path &" \ mytemp.xltx "" This sentence, "ThisWorkbook.Path" represents the path of the workbook where that template file and this file in the same directory. If you save the template to another location, such as "E: \ Templates", the code is:
Sheets.Add Type: = "E: \ Templates \ mytemp.xltx"

Example File: data sheet3.zip

Copyright © 2001-2014 Excel-Tool All Rights Reserved.

Copyright Excel-Tool All rights reserved