How to: Copy Worksheets

Applies to
The information in this topic applies only to the specified Visual Studio Tools for Office projects and versions of Microsoft Office. Project type Document-level projects Application-level projects Microsoft Office version Excel 2003 Excel 2007 For more information, see Features Available by Application and Project Type.

You can create a copy of a worksheet, and insert that worksheet before or after an existing worksheet in the workbook. If you do not specify where to insert the worksheet, Excel creates a new workbook to contain the new worksheet.

Note

Whether you copy the worksheet programmatically, or the end user copies the worksheet manually, there is no code behind the new worksheet and controls on the new worksheet do not function. This is because the newly copied worksheet is a Worksheet object and not a Microsoft.Office.Tools.Excel.Worksheet host item. Windows Forms controls and host controls can only be added to host items. For more information, see Programmatic Limitations of Host Items and Host Controls.

To add a copied worksheet to a workbook in a document-level customization

• Use the Copy(Object, Object) method to copy the first worksheet in the current workbook and place the copy after the third sheet.

  Globals.Sheet1.Copy(After:=Globals.ThisWorkbook.Sheets(3))
  

  Globals.Sheet1.Copy(missing, Globals.ThisWorkbook.Sheets[3]);
  

To add a copied worksheet to a workbook in an application-level add-in

• Use the Copy(Object, Object) method to copy the first worksheet in the current workbook and place the copy after the third sheet.

  Dim worksheet1 As Excel.Worksheet = CType(Application.ActiveWorkbook.Worksheets(1),  _
  Excel.Worksheet)
  Dim worksheet3 As Excel.Worksheet = CType(Application.ActiveWorkbook.Worksheets(3),  _
  Excel.Worksheet)
  worksheet1.Copy(After:=worksheet3)
  

  Excel.Worksheet worksheet1 = ((Excel.Worksheet)Application.ActiveWorkbook.Worksheets[1]);
  Excel.Worksheet worksheet3 = ((Excel.Worksheet)Application.ActiveWorkbook.Worksheets[3]);
  worksheet1.Copy(missing, worksheet3);
  

See Also

Tasks

How to: Add New Worksheets to Workbooks

How to: Delete Worksheets from Workbooks

How to: Select Worksheets

Concepts

Working with Worksheets

Host Items and Host Controls Overview

Global Access to Objects in Visual Studio Tools for Office Projects

Programmatic Limitations of Host Items and Host Controls

The Variable missing and Optional Parameters in Office Solutions

Other Resources

Excel Host Controls

Change History

Date	History	Reason
July 2008	Added a code example that can be used in an application-level add-in.	Customer feedback.