How to copy a sheet with a different name - C# and Excel Interop

Question

I would like to simply copy one sheet within my workbook and give it a different name.

var pointName1 = workbook.Worksheets["PointName1"] as Worksheet;
pointName1.Copy(); // How do I access this newly created sheet?

Ideally I would like be able to write a method like this

pointName1.CopyTo("New Sheet");

where 'New Sheet' is a renamed copy of 'PointName1'.

Sometimes PointName1 will be the only sheet in the workbook, other times there will be others.

Answer 1

You can achieve this in multiple ways - probably the easiest way is to copy after the last sheet and then rename it using the index:

Excel.Application xlApp = Marshal.GetActiveObject("Excel.Application") as Excel.Application;
Excel.Workbook xlWb = xlApp.ActiveWorkbook as Excel.Workbook;
Excel.Worksheet xlSht = xlWb.Sheets[1];
xlSht.Copy(Type.Missing, xlWb.Sheets[xlWb.Sheets.Count]); // copy
xlWb.Sheets[xlWb.Sheets.Count].Name = "NEW SHEET";        // rename

I believe this MSDN guide also answers your question.

If you want to get the index of a sheet, look up Worksheet.Index property.