Inserting Sheets

ActiveWorkbook

You can insert additional worksheets into the active workbook by adding to its sheets collection:

Application.ActiveWorkbook.Sheets.Add(Before, After, Count, Type) 

Since ActiveWorkbook is the default member of the Application object you can also use:

Application.Sheets.Add(Before, After, Count, Type) 

Since Application is the default member you could abbreviate it and use:

Sheets.Add(Before, After, Count, Type) 

Inserting a Worksheet

To insert a worksheet after the worksheet that is currently selected you can use:

Sheets.Add(Type:=xlSheetType.xlWorksheet) 

Because the sheet type xlWorksheet is the default you can shorten this to just:

Sheets.Add 

If you are copying or moving worksheets around in a workbook you cannot debug/step through the code.
Whenever referencing workbooks or worksheets always enclose them in single speech marks "WshName" as they could contain spaces and/or unusual characters.
If your VBA code is generating Dr Watsons or Application Errors when trying to select a worksheet, then the workbook may be corrupt. Copy all the worksheets to a new workbook.
To quickly view the event procedure for a worksheet right - click the Sheet tab and click View Code.
If you give your worksheet names using the Property window you can refer directly to the name in code instead of using Sheets("---").

It is not possible to add a chart sheet after the last worksheet in a workbook. You will have to insert it elsewhere and then move it.

Dim oWsh As Excel.Worksheet 
Set oWsh = Worksheets.Add 

Add a Sheet First

Set oFirst = ThisWorkbook.Worksheets(1) 
Set oNew = ThisWorkbook.Worksheets.Add(Before:= oFirst) 

Add a Sheet Last

Set oLast = ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count) 
Set oNew = ThisWorkbook.Worksheets.Add(After:= oLast) 

List all worksheets

For icount = 1 To ThisWorkbook.Worksheets.Count 
   Debug.Print ThisWorkbook.Worksheets(i).Name 
Next icount