Is there any way to copy cell data validation without using copy & pastespecial commands?
Is there any way to copy cell data validation without using copy & pastespecial commands?
(OP)
Hi
As per title, I was wondering if cell data validation settings be copied without a range().copy & range().pastespecial operation in Excel.
Its in an effort to solve a dreaded screen flicker that occurs when forced to copy/paste in this manner (yes I have Application.ScreenUpdating = false). Data validation on sheet(2) is applied to a similar set of cells in sheet(3)
An internet search doesn't turn anything up, no way of reading all the validation settings and then applying the same settings to destination cells, really after something like you can do with number formats using range().numberformat ideally, does anything exist?
Thanks in advance
As per title, I was wondering if cell data validation settings be copied without a range().copy & range().pastespecial operation in Excel.
Its in an effort to solve a dreaded screen flicker that occurs when forced to copy/paste in this manner (yes I have Application.ScreenUpdating = false). Data validation on sheet(2) is applied to a similar set of cells in sheet(3)
An internet search doesn't turn anything up, no way of reading all the validation settings and then applying the same settings to destination cells, really after something like you can do with number formats using range().numberformat ideally, does anything exist?
Thanks in advance





RE: Is there any way to copy cell data validation without using copy & pastespecial commands?
CODE -->
Range(Sheet1.Cells(1, 1), Sheet1.Cells(1, 21)).Copy _ Destination:=Sheet7.Cells(1, 1)RE: Is there any way to copy cell data validation without using copy & pastespecial commands?
I just want to copy the validation, value and number format. I can handle the number format and value without selecting cells and pasting. But not the data validation, so if anyone knows a way to do it without selecting a selection and pastespecial operation please let me know.
RE: Is there any way to copy cell data validation without using copy & pastespecial commands?
http://www.ozgrid.com/forum/showthread.php?t=60096
suggests that using copy and paste-special in VBA is the only way to do it.
I only did a brief search, but I didn't find anything to suggest otherwise.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Is there any way to copy cell data validation without using copy & pastespecial commands?
RE: Is there any way to copy cell data validation without using copy & pastespecial commands?
Check out the ScreenUpdating property.
Avoid using Activate and Select methods. Rather reference objects directly, like worksheets and ranges.
For tips from computer professionals, consider posting in Eng-Tips sister site, www.Tek-Tips.com in the Visual Basic for Applications (Microsoft) forum, http://www.tek-tips.com/threadminder.cfm?pid=707
Skip,
for a NUance!
RE: Is there any way to copy cell data validation without using copy & pastespecial commands?
Had some code where it would set it to true and then in a further called sub would set it back to false, before returning back to the original sub. Wasn't turning it on/off more than once or anything. Funny thing was in other code doing the same thing had no flashing, so who knows!