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

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.

You should be able to use the Copy function, but you won't be able to rename the sheet in the same step. The MSDN documentation shows the additional parameters:

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

From the documentation: If you do not specify either Before or After, Microsoft Office Excel creates a new workbook that contains the copied sheet.

To rename the sheet you'll need to get a reference 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 original sheet (since you're placing the copy before the original):

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

well, other solution mentioned here did not work for me. I got this solution.

Step 1: Create a copy of the source file (i.e. TempFile )

Step 2: Copy desired sheet of source file to TempFile

Step 3: Delete the source file

Step 4: Rename TempFile to Source File.

Note: You will need the "Microsoft.Office.Interop.Excel" package from Nuget for this solution. Also, add using Excel = Microsoft.Office.Interop.Excel;

static void Main(string[] args)
{
    Excel.Application excelApp;

    string sourceFileName = "Original.xlsx"; //Source excel file
    string tempFileName = "temp.xlsx";

    string folderPath = @"C:\FodlerPath\";

    string sourceFilePath = System.IO.Path.Combine(folderPath, sourceFileName);
    string destinationFilePath = System.IO.Path.Combine(folderPath, tempFileName);

    System.IO.File.Copy(sourceFilePath,destinationFilePath,true);

    /************************************************************************************/

    excelApp = new Excel.Application();
    Excel.Workbook wbSource, wbTarget;
    Excel.Worksheet currentSheet;

    wbSource = excelApp.Workbooks.Open(sourceFilePath);
    wbTarget = excelApp.Workbooks.Open(destinationFilePath); 

    currentSheet = wbSource.Worksheets["Sheet1"]; //Sheet which you want to copy
    currentSheet.Name = "TempSheet"; //Give a name to destination sheet

    currentSheet.Copy(wbTarget.Worksheets[1]); //Actual copy
    wbSource.Close(false);
    wbTarget.Close(true);
    excelApp.Quit();

    System.IO.File.Delete(sourceFilePath);
    System.IO.File.Move(destinationFilePath, sourceFilePath);
}