error when copying worksheet

Jun 25, 2012 at 7:55 PM

I have successfully opened a workbook and added a couple of worksheets.  However, within a For loop, I am trying to copy a worksheet that I am using as a Template.  On the very first iteration, I receive the following error, which looks more like it is a problem with the Rows collection than the Worksheets collection.  I have tested to see if the worksheet exists in the collection and it does not.  So, I am confused about how to proceed.  Has anyone else had this problem?

The error occurs on this call:


ws As IXLWorksheet = wsTemplate.CopyTo("New Template")

System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.ArgumentException: An item with the same key has already been added.    at System.ThrowHelper.ThrowArgumentException(ExceptionResource resource)    at System.Collections.Generic.Dictionary`2.Insert(TKey key, TValue value, Boolean add)    at System.Collections.Generic.Dictionary`2.Add(TKey key, TValue value)    at ClosedXML.Excel.XLRowsCollection.Add(Int32 key, XLRow value)    at ClosedXML.Excel.XLWorksheet.<>c__DisplayClass39.<CopyTo>b__35(KeyValuePair`2 kp)    at ClosedXML.Excel.Extensions.ForEach[T](IEnumerable`1 source, Action`1 action)    at ClosedXML.Excel.XLWorksheet.CopyTo(XLWorkbook workbook, String newSheetName, Int32 position)    at ClosedXML.Excel.XLWorksheet.CopyTo(String newSheetName)    at DCIS.Service.RunExcelReports(String fy, String installation) in C:\WebSites\DCIS_2011\App_Code\Service.vb:line 3285    --- End of inner exception stack trace ---


Jon Rosenblum


Jun 26, 2012 at 3:11 PM

The line of code you are using makes a copy of the worksheet wsTemplate back into the same workbook that it came from and gives it the name "New Template". It seems that there already exists a worksheet with the name "New Template" in the source workbook. If you want to add the worksheet to a different workbook, you need to create a new workbook and do this,

wsTemplate.CopyTo(newWb, "New Template")

See the documentation page called  Copying Worksheets

Jun 26, 2012 at 4:28 PM

I have tried several different methods of copying the worksheet and it doesn't matter how I do it, I still receive the error.  I have successfully copied many other worksheets, just not this template sheet.  As I stated earlier, I have tested before copying the worksheet and the template worksheet does not exist in the workbook before I try to copy it.  So, I still think there is a problem.  Is it possible there is a problem within the worksheet that some of the rows do not copy properly (which could explain the RowsCollection.Add error listed above)?  The template sheet has many formulas and named ranges and to recreate that sheet at runtime would take quite a bit of processing time during the data load.

Thanks again for taking the time!

Jan 30, 2014 at 2:50 PM
Edited Jan 31, 2014 at 1:25 AM
Has anyone found a solution to this issue? I have the same problem. I have 1 workbook with several worksheets. One of the worksheets is a template that is copied the number of times needed.

I get the error "An item with the same key has already been added" every time I try to copy a sheet. Changing the name of the copy doesn't help. I don't see where to set a key that it is finding a duplicate.

Anyone else have this problem? Thanks!

After doing some more testing, I noticed that I can copy any other worksheet or a new blank worksheet without problems but if the worksheet being copied has a comment attached to a cell, the copy fails. Is there a problem with copying a worksheet with comments?