Be the first user to complete this post

  • 0
Add to List

VBA-Excel: Create worksheets with Names in Specific Format/Pattern.

Download Link:Worksheets

Many times we find ourselves in a situation where we want to allow users to add the new worksheets and the sheet name must follow some specific format or pattern.

Recently I have received the same request from one our reader. This made me realize that it's a very common problem so I wrote this article to solve such problems.

Download the workbook from the download link at the start and end of this article.

I am putting the code here as well so that you can understand it better and modify it as per your needs.


Worksheet Name format: FY_xxxx-xx (i.e FY_2013-14)

Complete Code:

Sub sumit()
Dim mainWorkBook As Workbook
Set mainWorkBook = ActiveWorkbook
myValue = InputBox("Enter Sheet name:")
Set objRegExp = CreateObject("vbscript.regexp")
objRegExp.Global = True
objRegExp.Pattern = "FY_\d{4}-\d{2}$"
Set regExpMatches = objRegExp.Execute(myValue)
If regExpMatches.Count = 1 Then
	mainWorkBook.Worksheets.Add().Name = myValue
	MsgBox ("New Work Sheet with name " & myValue & " is created")
	MsgBox ("Please Enter the Sheet name in FY_xxxx_xx format")
End If
End Sub

Screen Shots:

Invalid Sheet Name:

Invalid sheet name
Invalid sheet name

Valid Sheet Name:

Valid Sheet Name

Thanks Venkat for suggesting this post. Looking for more.

Download Link:Worksheets

Also Read:

Also Read:

  1. VBA-Excel: Clear data from Excel Sheet(WorkSheet)
  2. VBA-Excel: Get the Instance of already opened word document
  3. VBA-Excel: Add/Insert multiple Images/Pictures from a folder in Word Document
  4. VBA-Excel : 3D-Ranges – FillAcrossSheets Method
  5. VBA-Excel: Get all the WeekDays or Working days in Specified Date Range, (excluding Satudays and Sundays)
  6. VBA-Excel: Consolidator – Merge or Combine Multiple Excel Files Into One
  7. VBA-Excel: Appending Text to an Existing Word Document - at the End
  8. VBA-Excel: Copy/Paste data - Copy the Entire data from one sheet to another