Adding a sheet in Already existing workbook is very easy. It can be done manually easily. Here we are going to learn, how to add a Sheet in an already existing workbook using Excel Macro. For doing so, there can be many possibilities.
 
For example:  we might want to add a new sheet always with certain format. In this case, either we first add a new Sheet to the workbook without any formatting and then do the same formatting using macro, or make a Template (Sheet) which has exactly the same format what you want in each sheet and then copy the same Template each time and then rename the sheet name with the given name.

In some cases, you might just want to add a new sheet (without any formatting) with a name given by user. In this case you can just add a new sheet using macro and then rename the sheet name with the given name by user.
 
Note: While adding a new Sheet in Excel, MS Excel never ask for a name of that new Sheet; It always gives it’s own generated name like Sheet 1, Sheet 2, Sheet 3….etc. So when you want to add a New sheet with your own Name, First add a Sheet and then Rename it with your given Name. This is the reason, you can see in both the below example we are first adding a new Sheet and then renaming it.
 

i) When you have already built template for the sheet:

Sub addsheet(VariableSheetName)
' *****************************************************
' Before adding any sheet in to workbook we need
' to make sure that there is no sheet already there
' in the work book with same name.
' *****************************************************
Dim SheetExists as Boolean
SheetExists = False
For Each Sheet In Sheets
If Ucase(Sheet.Name ) = Ucase(VariableSheetName) Then
SheetExists = True
Msgbox("This Sheet already exists")
End If
Next Sheet
If (SheetExists = False) Then
Worksheets("Expences-Template").Copy After:=Worksheets("Expences-Template")
Worksheets("Expences-Template (2)").Name = VariableSheetName
End If
Worksheets(VariableSheetName).Activate
End Sub

ii) When you have to simply add a sheet with desired name

Sub AddSheet(VariableSheetName)
' *****************************************************
' Before adding any sheet in to workbook we need
' to make sure that there is no sheet already there
' in the work book with same name.
' *****************************************************
Dim SheetExists as Boolean
SheetExists = False
For Each Sheet In Sheets
If Ucase(Sheet.Name ) = Ucase(VariableSheetName) Then
SheetExists = True
MsgBox("This Sheet already exists")
End If
Next Sheet
If (SheetExists = False) Then
Worksheets.Add().Name = VariableSheetName
Worksheets(VariableSheetName).Select
End Sub