Excel VBA: Adding a Worksheet After an Active Sheet

This Excel VBA macro inserts a worksheet after an active sheet.

Macro Example

Sub InsertsWorksheetAfterActiveSheet()

  Worksheets.Add After:=ActiveSheet

End Sub

Description

[Application.]Worksheets

  Worksheets...

The [Application.]Worksheets property returns the Sheets collection object that represents all the worksheets (without chart-sheets) in the workbook.

obj-Excel-Application-Worksheets

The Worksheets property is a member of the Excel.Global class, so we can omit the Application property.

obj-Excel-Worksheets

Worksheets.Add

  Worksheets.Add After:=ActiveSheet

We can insert a new worksheet after a specified sheet by the Worksheets.Add method with setting the After parameter.

In this macro, I am setting the ActiveSheet property to the After parameter. So the new worksheet will be created after an active sheet.

Sub InsertsWorksheetAfterActiveSheet()

  Worksheets.Add After:=ActiveSheet

End Sub

Properties

http://www.relief.jp/itnote/archives/003374.php

Apply to

  • Excel 2013
  • Excel 2010
  • Excel 2007