Adding sheet with name using VBA

Adding worksheets to Excel is very simple. For example, to add a Worksheet after the active sheet (default unless stated otherwise) this is the default code when u record a macro.

Sub add_worksheet()
Sheets("current sheet").Select

Sheets.Add

Sheets("Sheet9").Select

Sheets("Sheet9").Name = "my sheet"
End sub

But when u want to use this macro as default macro this will not work as the sheet added will be not be "sheet9" every time So possibility here is that we have to give a name to sheet while adding.

This little piece of code solves our purpose:-

Sub add_worksheet()
Sheets.Add.name=("my sheet")
End sub
    
But this will add sheet before active sheet,if we want sheet in the end like default then:-

Sub add_worksheet()
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "my sheet"
End sub

Default syntax for sheet add method is sheets.Add(Before,After,Count,Type).We can either use before or after one time.The only other Variant we can use if desired is the Type Variant. The Type specifies the sheet type. Can be one of the following XlSheetType constants: xlWorksheet, xlChart, xlExcel4MacroSheet, or xlExcel4IntlMacroSheet. 
If you are inserting a sheet based on an existing template, specify the path to the template (Recording a macro is best for this). The default value is xlWorksheet.