How To Add Worksheet in VBA

Adding worksheets to Excel is very simple. For example, to add a Worksheet after the active sheet (default unless stated otherwise), name it "MySheet" and have it become the active sheet, you would use some code like shown below

1 2 3

Sub Simple1() Worksheets.Add().Name = "MySheet" End Sub

If we wanted to add a Worksheet as the last Worksheet and name it "MySheet" we would use

1 2 3 4 5 6 7

Sub Simple2() Dim ws As Worksheet With ThisWorkbook Set ws = .Sheets.Add(After:=.Sheets(.Sheets.Count)) ws.Name = "MySheet2" End With End Sub

And, below code use with checking sheet already exist or not.
If exist adding sheet will skip.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

Sub Simple3() Dim ws As Worksheet Dim wsName As String Dim Flag As Boolean Flag = True wsName = "MySheet3" With ThisWorkbook For Each ws In Worksheets If ws.Name = wsName Then Flag = False End If Next ws If Flag Then Set ws = .Sheets.Add(After:=.Sheets(.Sheets.Count)) ws.Name = wsName MsgBox wsName & " Sheet already created" Else MsgBox wsName & " Sheet already exist" End If End With End Sub

Download File