How to: Programmatically copy worksheets

  • [アーティクル]

Applies to: yesVisual Studio noVisual Studio for Mac

Note

This article applies to Visual Studio 2017. If you're looking for the latest Visual Studio documentation, see Visual Studio documentation. We recommend upgrading to the latest version of Visual Studio. Download it here

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.

Applies to: The information in this topic applies to document-level projects and VSTO Add-in projects for Excel. For more information, see Features available by Office application and project type.

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 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

  1. Use the Copy method to copy the first worksheet in the current workbook and place the copy after the third sheet.

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

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

To add a copied worksheet to a workbook in a VSTO Add-in

  1. Use the Copy method to copy the first worksheet in the current workbook and place the copy after the third sheet.

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

    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)

See also