Show Buttons
Share On Facebook
Share On Twitter
Share On Google Plus
Share On Linkdin
Share On Reddit
Contact us
Hide Buttons

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

 

Down­load Link:Work­sheets

Many times we find our­selves in a sit­u­a­tion where we want to allow users to add the new work­sheets and the sheet name must fol­low some spe­cific for­mat or pattern.

Recently I have received the same request from one our reader. This made me real­ize that it’s a very com­mon prob­lem so I wrote this arti­cle to solve such problems.

Down­load the work­book from the down­load link at the start and end of this article.

I am putting the code here as well so that you can under­stand it bet­ter and mod­ify it as per your needs.

Exam­ple:

Work­sheet Name for­mat: FY_xxxx-xx (i.e FY_2013-14)

Com­plete 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")
    Else
     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 sug­gest­ing this post. Look­ing for more.

Down­load Link:Work­sheets

Also Read:

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: