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

Question

I would just like to copy one sheet in my book 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 to write a way like this

pointName1.CopyTo("New Sheet");

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

Sometimes PointName1 will be the only sheet in the book, in other cases there will be others.

Answer 1

You can achieve this in several ways - perhaps the easiest way - 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 sheet index, find the Worksheet.Index property .

Answer 2

You can use the copy function, but you cannot rename the sheet in the same step. The MSDN documentation shows additional options:

 pointName1.Copy(pointName1, Type.Missing); //Place a copy before the existing sheet 

From the documentation: If you do not specify before or after, Microsoft Office Excel creates a new workbook containing the copied sheet.

To rename a sheet, you will need to get a link to the new sheet (by index or name) and use the Name property of worksheet to change the name.

EDIT:

If you use the code above, you can use the index of the source sheet (since you put a copy in front of the original):

 int index = pointName1.Index; pointName1.Copy(pointName1, Type.Missing); Worksheet newWS = (Worksheet)xlApp.Worksheets[index]; 

Answer 3

The easiest way is to copy it after the last sheet presented in the accepted answer.

Note that if the excel file contains hidden sheets, a copy will be placed between the visible sheets and the hidden sheets, so the hidden sheets will be shifted to the right.

If you then try to set the name using xlWb.Sheets[xlWb.Sheets.Count].Name = "NEW SHEET" , you will rename your last hidden sheet instead of your new copy.

I managed to get around this by contacting a new copy by its name: xlWb.Sheets["_oldName_ (2)"].Name = "NEW SHEET" .

Another fix that will allow you to use xlWb.Sheets[xlWb.Sheets.Count].Name = "NEW SHEET" is to set all sheets before copying the desired sheet.