Excel Macro: Copying an Active Sheet to the Last

This Excel VBA macro copies an active sheet to the last of the active workbook.

Macro Example

Sub CopyActiveSheetToLast()

  ActiveSheet.Copy After:=Sheets(Sheets.Count)

End Sub

Description

[Application.]ActiveSheet

  ActiveSheet...

The [Application.]ActiveSheet property returns an active Worksheet or Chart object. So the Object Browser shows us that returned value is “Object”.

obj-Excel-Application-ActiveSheet

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

obj-Excel-Global-ActiveSheet

Worksheet.Copy (ActiveSheet.Copy)

  ActiveSheet.Copy...

The ActiveSheet.Copy (Worksheet.Copy method) copies the sheet to another location.

obj-Excel-Worksheet-Copy

Worksheet.Copy After:=

  ActiveSheet.Copy After:=...

The Worksheet.Copy‘s parameter:After specifies the sheet after which the copied sheet will be placed.

Sheets(Sheets.Count)

  ActiveSheet.Copy After:=Sheets(Sheets.Count)

The Sheets.Count property returns the sheets count.

obj-Excel-Sheets-Count

So, the Sheets(Sheets.Count) property returns the last sheet.

Finally, the code:ActiveSheet.Copy After:=Sheets(Sheets.Count) copies an active sheet to the last.

Sub CopyActiveSheetToLast()

  ActiveSheet.Copy After:=Sheets(Sheets.Count)

End Sub

Properties

http://www.relief.jp/itnote/archives/excel-vba-sheet-copy.php

Apply to

  • Excel 2013
  • Excel 2010
  • Excel 2007