This project has moved. For the latest updates, please go here.

How to copy data validations?

Jan 30, 2012 at 2:33 PM

I'm pretty sure it worked few weeks ago.

I'm trying to copy a column with styles, values etc. to a newly inserted column. For performance reasons I use fixed-size ranges instead of whole columns. Some cells have named data validations such as dropdown list that point to a range of values from another sheet. (Data validation for such cell is defined as List with value e.g. "=DDL_STATION").

Everything is pasted well but cell (column) width and these data validations.

How to copy data validations from one range to another or from one column to another?

I tried:
templateRange.CopyTo(wsRel.Cell(1, newRelationColIndex)); 
wsRel.Cell(1, newRelationColIndex).Value = templateRange; 

 

Coordinator
Jan 31, 2012 at 4:39 AM

This is a new error. Data validations are copied when you copy a worksheet but we copying a range seems to have problems. I'll work on it...

Coordinator
Jan 31, 2012 at 4:39 AM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.
Mar 9, 2012 at 4:46 PM
It seems that blank cells with data validation (no value, only data validation) are not retaining the data validation after being copied to another worksheet. As soon as I try to copy a cell with some value + data validation, everything gets copied correctly. Is this something you are planning to fix?

My code is something like below:

foreach (IXLColumn column in sheet1.ColumnsUsed().Where(r => r.FirstCell().GetString() == "abc"))
{
IXLRange range = column.AsRange();

range.CopyTo(sheet2.Column(counter + 1));

sheet2.Column(counter + 1).AdjustToContents();
}

wb2.SaveAs("xyz.xlsx");