Copy Worksheet between Workbooks doesn't work with Ranges

[ydbendasan]

when copying a worksheet from a workbook that has named ranges to another workbook, the Named Ranges get copied but when you try

var sourceFile= new Workbook(@"c:\test1.xlsx");
var destinationFile= new Workbook(@"c:\test2.xlsx");

var newSheetIndex = destinationFile.Worksheets.Add();workbook.Worksheets.GetRangeByName
using (var newSheet = destinationFile.Worksheets[newSheetIndex])
{
                newSheet.Copy(sourceFile.Worksheets[sourceSheetIndex]);               
                destinationFile.Save(@"c:\test2.xlsx");
}
var range = destinationFile.Worksheets.GetRangeByName(rangeName);

throws a null exception at the GetRangeByName call. also, if you look at the newly created worksheet, it's Workbook property is set to null

[shakeel-faiz]

@ydbendasan This is happening because of C# using keyword. Please delete using keyword and its block. When you use using, then it destroys the workbook object, so using block is good for only those scenarios in which you want to destroy Workbook. After removing using block, your code will look like this and it will run fine.

C#

var sourceFile = new Workbook(@"c:\test1.xlsx");
var destinationFile = new Workbook(@"c:\test2.xlsx");

var newSheetIndex = destinationFile.Worksheets.Add();
var newSheet = destinationFile.Worksheets[newSheetIndex];

newSheet.Copy(sourceFile.Worksheets[sourceSheetIndex]);
destinationFile.Save(@"c:\test2.xlsx");

var range = destinationFile.Worksheets.GetRangeByName(rangeName);